Be Excellent To Each Other https://www.beexcellenttoeachother.com/forum/ |
|
Very basic Visual Basic help needed https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=1022 |
Page 1 of 1 |
Author: | Curiosity [ Tue Jun 17, 2008 12:16 ] |
Post subject: | Very basic Visual Basic help needed |
Hello lovely helpful people. I'm not very good at VB in Excel, as I have forgotten how to use it over the years. I'm writing a macro which will look at a column, and if the column is blank it will insert today's date. That much is easy and I can do. However, the field is a date field, and thusly if it gets a return of null is shows up as "00/01/1900", and as such my macro misses it. I have tried to work out how to reference a date within the statement, but am drawing a blank, and the help function is beyond useless. So, I have something along these lines: Code: Sub FixTargetDate07() Dim a As Integer a = 2 Do If Cells(a, 6) = "00/01/1900" Then Cells(a, 6) = Date a = a + 1 Else a = a + 1 End If Loop Until a = 5000 End Sub I've tried adding a ".Value" to the Cells command at the start, removing the quote marks, etc etc etc. This is bound to be soemthing simple. Can someone just give me the answer please? |
Author: | GazChap [ Tue Jun 17, 2008 12:49 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Try this: Code: Sub FixTargetDate07()
Dim a As Integer Dim cellDate as String a = 2 Do cellDate = Format(Cells(a, 6), "dd/MM/YYYY") If cellDate = "00/01/1900" Then Cells(a, 6) = Date a = a + 1 Else a = a + 1 End If Loop Until a = 5000 End Sub |
Author: | Bluecup [ Tue Jun 17, 2008 12:54 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Not sure if this will work with how you described but i think it will. Code: Sub test() Selection.SpecialCells(xlCellTypeBlanks).Value = Date End Sub Select/highlight the cells you want to test then run the macro. |
Author: | Curiosity [ Tue Jun 17, 2008 13:11 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Thanks for the help thus far, but neither work. GazChap - This gives me a type mismatch on the first line of the 'Do' command when I run the macro. Bluecup - Not sure if it will work or not, but this is tied into a bunch of other macros and needs to run automatically without selecting stuff. Thanks to both though. Any other takers? Basically all I need to know is how to ask a cell if it has a specific date in it. |
Author: | GazChap [ Tue Jun 17, 2008 13:16 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Try changing cellDate = Format(Cells(a, 6), "dd/MM/YYYY") to cellDate = Format(Cells(a, 6).Value, "dd/MM/YYYY") then. There should be no type mismatch as the Format function returns a String, which is what I've Dimmed cellDate as. So the type mismatch must be coming from Cells(a, 6) |
Author: | Bluecup [ Tue Jun 17, 2008 13:16 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Ok, what about this Code: Sub test()
Range("A2:A5000").Select Selection.SpecialCells(xlCellTypeBlanks).Value = Date End Sub |
Author: | Mr Cochese [ Tue Jun 17, 2008 13:18 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Have you tried CDate() on the cell value? |
Author: | Cras [ Tue Jun 17, 2008 13:21 ] |
Post subject: | Re: Very basic Visual Basic help needed |
GazChap wrote: Try changing cellDate = Format(Cells(a, 6), "dd/MM/YYYY") to cellDate = Format(Cells(a, 6).Value, "dd/MM/YYYY") then. There should be no type mismatch as the Format function returns a String Not if it's null, it doesn't. Try: Code: If IsNull(cells(a,6).value) then
... ... |
Author: | GazChap [ Tue Jun 17, 2008 13:26 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Craster wrote: Not if it's null, it doesn't. Null really does ruin it for everyone |
Author: | Curiosity [ Tue Jun 17, 2008 13:27 ] |
Post subject: | Re: Very basic Visual Basic help needed |
GazChap - same error Bluecup - That selects all of the cells but doesn't alter them. The problem I have is that if the cells were blank I would know how to change them. As it is, I want to change only the ones that house a value of "00/01/1900". Mr Cochese - I don't know what that does Craster - The problem being that the cells aren't null, they have in them the impossible date of "00/01/1900", which is causing the problem. What's the standard format for: If 'this cell' = 'this date' then Do this stuff. ? many thanks for the attempted help |
Author: | Bluecup [ Tue Jun 17, 2008 13:30 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Sorry, I had a feeling I misunderstood the question. What about If some_cell= 0 then ... ? (Because 0 = 0/1/1900 in date format) Or test if the cell is null directly If isnull(some_cell) then... |
Author: | GazChap [ Tue Jun 17, 2008 13:30 ] |
Post subject: | Re: Very basic Visual Basic help needed |
What version of Excel are you running here? Can you send me the .xls with it all in? |
Author: | Curiosity [ Tue Jun 17, 2008 13:39 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Bluecup wrote: Sorry, I had a feeling I misunderstood the question. What about If some_cell= 0 then ... ? Amusingly, it works when you just put the field value to zero, along these lines. So: Code: Do If Cells(a, 6).Value = 0 Then Cells(a, 6) = Date a = a + 1 ...for the middle bit. I feel like a retard, as not only should this have been one of the first things I tried, but also some, if not all of the type mismatch errors were probably caused by a few rogue #VALUE! data points within the 5000 cells. I'm using Excel 2007, for the record. I ran the same spreadsheet in Excel 2003 and it didn't need to be altered as it saw the nulls as null, whereas 2007 sees the nulls as 0s. Sadly we have to use 2007 as otherwise it doesn't integrate with our Sharepoint database. Many thanks to all offering help. You are all excellent. |
Author: | Cras [ Tue Jun 17, 2008 13:41 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Curiosity wrote: I'm using Excel 2007, for the record. I ran the same spreadsheet in Excel 2003 and it didn't need to be altered as it saw the nulls as null, whereas 2007 sees the nulls as 0s. Sadly we have to use 2007 as otherwise it doesn't integrate with our Sharepoint database. Now go away and repeat this a thousand times: "Null and empty are not the same thing". |
Author: | Curiosity [ Tue Jun 17, 2008 13:55 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Craster wrote: Curiosity wrote: I'm using Excel 2007, for the record. I ran the same spreadsheet in Excel 2003 and it didn't need to be altered as it saw the nulls as null, whereas 2007 sees the nulls as 0s. Sadly we have to use 2007 as otherwise it doesn't integrate with our Sharepoint database. Now go away and repeat this a thousand times: "Null and empty are not the same thing". Meh. They've altered a basic functionality of dealing with them between the versions of Excel, and made it more difficult. That's a bad move in my book. How can an empty field suddenly be given a value? It's stupid. That said, the entire spreadsheet looks like it might be a bust. It's based upon regularly exporting from Sharepoint into Sheet One of a workbook. Sheet Two then has all the forumlae and macros working to tidy up and monkey around with the data. Sadly, for some reason, when you overwrite the data in Sheet One it completely removes all of the bloody references in Sheet Two that I just spent ages setting up. *hits computer with axe* |
Author: | mrbogus [ Tue Jun 17, 2008 16:13 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Curiosity wrote: How can an empty field suddenly be given a value? It's stupid. Probably something to do with .NET, since a date in .NET is a value type and can't be null (only reference types, or objects, can be null). I'd argue that type safety is a Good Thing, although the use of an illegal date as the null equivalent is undoubtedly a Stupid Thing. Incidentally, dates are stored in the format ddddd.ttttt, where ddddd represents the number of days since 0/0/1900, and tttttt represents (I think) the fractional number of days (if you're dealing with hours/minutes/seconds). |
Author: | Cras [ Tue Jun 17, 2008 16:23 ] |
Post subject: | Re: Very basic Visual Basic help needed |
mrbogus wrote: Curiosity wrote: How can an empty field suddenly be given a value? It's stupid. Probably something to do with .NET, since a date in .NET is a value type and can't be null (only reference types, or objects, can be null). I'd argue that type safety is a Good Thing, although the use of an illegal date as the null equivalent is undoubtedly a Stupid Thing. Incidentally, dates are stored in the format ddddd.ttttt, where ddddd represents the number of days since 0/0/1900, and tttttt represents (I think) the fractional number of days (if you're dealing with hours/minutes/seconds). VBA in Office 2007 is still VB6, not .NET. |
Author: | Curiosity [ Tue Jun 17, 2008 16:28 ] |
Post subject: | Re: Very basic Visual Basic help needed |
I'm largely blaming all of today's woe (there's so much more of it) on Microsoft Sharepoint exporting data like a bag full of shite. |
Author: | Cras [ Tue Jun 17, 2008 16:29 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Curiosity wrote: I'm largely blaming all of today's woe (there's so much more of it) on Microsoft Sharepoint being a bag full of shite. FTFY. |
Author: | Curiosity [ Tue Jun 17, 2008 16:33 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Craster wrote: Curiosity wrote: I'm largely blaming all of today's woe (there's so much more of it) on Microsoft Sharepoint being a bag full of shite. FTFY. Indeed... though it certainly keeps me in work. |
Author: | Joans [ Tue Jun 17, 2008 17:56 ] |
Post subject: | Re: Very basic Visual Basic help needed |
Not sure if you've fixed this yet, but I've blatantly stolen GazChap's code and made a subtle change. This won't help if you're still getting the datatype mismatch though. Can you pass the value from cells(a,6) into a variable and see what it is? Code: Sub FixTargetDate07()
Dim a As Integer Dim cellDate as String a = 2 Do cellDate = Format(Cells(a, 6), "dd/MM/YYYY") If cellDate = "31/12/1899" Then Cells(a, 6) = Date a = a + 1 Else a = a + 1 End If Loop Until a = 5000 End Sub |
Page 1 of 1 | All times are UTC [ DST ] |
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ |