Date Functions (VB/EXCEL)

[StefanRSA]

Hi Guys,

I am trying to make the below code snippet work to return a date if all arguments are positive. Unfortunately, instead of returning the date, this function returns "". Is there something small I have missed out here?

Should I rather use .FormulaR1C1 = "=TODAY()"? Or is this because I have tried to do the Date function within a WITH statement.Interior?

'If rejected and resolved return todays date
If Left(Cells(ifindrow, iColNum(8) - 1).Value, 3) = "Rej" And Cells(ifindrow, iColNum(8)).Value = "Yes" Then
With Cells(ifindrow, iColNum(8) + 1).Interior
.ColorIndex = xlNone
.Value = Date
End With
End If

Many thanks for your consideration.

Kind Regards,

Stefan

 

[njimack]

You're trying to apply the date to the interior colour of the cell. Try this:


If Left(Cells(ifindrow, iColNum(8) - 1).Value, 3) = "Rej" And Cells(ifindrow, iColNum(8)).Value = "Yes" Then
With Cells(ifindrow, iColNum(8) + 1)
.Interior.ColorIndex = xlNone
.Value = Date
End With
End If

 

[StefanRSA]

Thanks for the reply Neil, but I still have "" returned. Is there any other reason this should happen?

Im working with a document filled to the brim with code and it could very well be another macro overwriting this one to ""....

 

[Richard Schollar]

Hello Stefan

If you are not getting the date in your cell(s) then it suggests the If test is returning False - check here first to make sure it is working as expected. Step thru the code using F8 so you can check the values of each cell

 

[StefanRSA]

Hi Richard,

Thank you for this insight!

I think you may be right, perhaps if I specified an ELSE statement I would fix the FALSE return. I'll let you know how it goes...

 

[StefanRSA]

Hi Richard,

I tried putting in the ELSE statement and that didnt seem to solve the problem. So I went through the surrounding code snippetts and it turns out one of the other statements further in was clearing that box. So the date was being printed, but then cleared by another function.

Thank you Rich and Neil for your help!!