Very basic Visual Basic help needed

Curiosity · **Posted:** Tue Jun 17, 2008 12:16

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?

GazChap · **Posted:** Tue Jun 17, 2008 12:49

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

Bluecup · **Joined:** 31st Mar, 2008 **Posts:** 925

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.

Curiosity · **Posted:** Tue Jun 17, 2008 13:11

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 · **Posted:** Tue Jun 17, 2008 13:16

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)

Bluecup · **Joined:** 31st Mar, 2008 **Posts:** 925

Ok, what about this

Code:

Sub test()
     Range("A2:A5000").Select
     Selection.SpecialCells(xlCellTypeBlanks).Value = Date
End Sub

Mr Cochese · **Joined:** 28th May, 2008 **Posts:** 75

Have you tried CDate() on the cell value?

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49244

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
...
...

GazChap · **Posted:** Tue Jun 17, 2008 13:26

Craster wrote:

Not if it's null, it doesn't.

Null really does ruin it for everyone

Curiosity · **Posted:** Tue Jun 17, 2008 13:27

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 · **Joined:** 31st Mar, 2008 **Posts:** 925

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...

GazChap · **Posted:** Tue Jun 17, 2008 13:30

What version of Excel are you running here? Can you send me the .xls with it all in?

Curiosity · **Posted:** Tue Jun 17, 2008 13:39

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.

:DD

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49244

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".

Curiosity · **Posted:** Tue Jun 17, 2008 13:55

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*

mrbogus · **Joined:** 30th Mar, 2008 **Posts:** 112

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).

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49244

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.

Curiosity · **Posted:** Tue Jun 17, 2008 16:28

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.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49244

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.

Curiosity · **Posted:** Tue Jun 17, 2008 16:33

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.

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8655

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

Be Excellent To Each Other

Very basic Visual Basic help needed
For Microsoft Excel

Who is online

Be Excellent To Each Other

Very basic Visual Basic help needed For Microsoft Excel

Who is online

Very basic Visual Basic help needed
For Microsoft Excel