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:
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?
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
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?
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
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
Not sure if this will work with how you described but i think it will.
Select/highlight the cells you want to test then run the macro.
Code:
Sub test()
Selection.SpecialCells(xlCellTypeBlanks).Value = Date
End Sub
Selection.SpecialCells(xlCellTypeBlanks).Value = Date
End Sub
Select/highlight the cells you want to test then run the macro.
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.
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.
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)
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)
Ok, what about this
Code:
Sub test()
Range("A2:A5000").Select
Selection.SpecialCells(xlCellTypeBlanks).Value = Date
End Sub
Range("A2:A5000").Select
Selection.SpecialCells(xlCellTypeBlanks).Value = Date
End Sub
Have you tried CDate() on the cell value?
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
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
...
...
...
...
Craster wrote:
Not if it's null, it doesn't.
Null really does ruin it for everyone
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
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
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...
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...
What version of Excel are you running here? Can you send me the .xls with it all in?
Bluecup wrote:
Sorry, I had a feeling I misunderstood the question.
What about
If some_cell= 0 then ... ?
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
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.
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".
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*
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).
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.
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.
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.
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.
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?
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
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 [ 21 posts ]