CODEWITHSUNDEEP
vbaexcel
IT_User
IT_User

Reputation: 779

vba - Range loop provided Jan1900 date error

I have been working with members of the stack exchange community over the past 4 days while I work extensively in VBA (so ive been using vba for 4 days :) ). I had an issue that required me to take the date that was being presented and standardize the format (dates were different throughout sheet). vba - set data in cell to what date is being displayed

This worked for what I was trying to accomplish. There was a side-effect where if a cell contained no data, it would default to 01/Jan/1900.

Code is below.

timestart = 1      #Column 1
completeBad = 2    #Column 2
timeSet = 24       #Column 24
completeTime = 25  #Column 25
lastrow = 1000

Range(Cells(2, timeSet), Cells(lastrow, completeTime)).NumberFormat = "@"
Range(Cells(2, timeSet), Cells(lastrow, timeSet)).NumberFormat = "@"
Range(Cells(2, timestart), Cells(lastrow, timestart)).Value = Evaluate("""'"" & INDEX(TEXT(" & Range(Cells(2, timestart), Cells(lastrow, timestart)).Address(0, 0) & ",""dd/mmm/yyyy""),)")
Range(Cells(2, completeBad), Cells(lastrow, completeBad)).Value = Evaluate("""'"" & INDEX(TEXT(" & Range(Cells(2, completeBad), Cells(2, completeBad)).Address(0, 0) & ",""dd/mmm/yyyy""),)")
For Q = 2 To lastrow
  Cells(Q, timeSet).Value = Cells(Q, timestart).Value
  Cells(Q, completeTime).Value = Cells(Q, completeBad).Value
next Q

Nothing crazy going on here. Justs sets the format/text of a cell, and copies the data to a cell that is set up for text.

This gives me a lot of cells with 01/Jan/1900 being displayed if the timestart and completeBad cells have no data in them.

I am trying to get this to loop through each cell. My code is below:

timestart = 1      #Column 1
completeBad = 2    #Column 2
timeSet = 24       #Column 24
completeTime = 25  #Column 25
lastrow = 1000
For Q = 2 To lastrow
If Cells(Q, timestart) <> "" Then
  Range(Cells(Q, timestart), Cells(Q, timestart)).Value = Evaluate("""'"" & INDEX(TEXT(" & Range(Cells(Q, timestart), Cells(Q, timestart)).Address(0, 0) & ",""dd/mmm/yyyy""),)")
  Cells(Q, timeSet).Value = Cells(Q, timestart).Value
End If
If Cells(Q, projCom) <> "" Then
  Range(Cells(Q, completeBad), Cells(Q, completeBad)).Value = Evaluate("""'"" & INDEX(TEXT(" & Range(Cells(Q, completeBad), Cells(Q, completeBad)).Address(0, 0) & ",""dd/mmm/yyyy""),)")
  Cells(Q, completeTime).Value = Cells(Q, completeBad).Value
End If

I tried to use range to select a single cell as at least with range the code was modifying my data for me.

This returns #VALUE! in all of my cells (for my date column I call). I have tried multiple ways to only modify one cell at a time and I keep receiving #VALUE! or it errors out on my code and I cannot figure out why. Another example that does not work for my loop is below. Can someone shed some light on what I am doing wrong with calling either the full code above or my code below (code below replaced the range used in my for loop, I just didn't want to retype everything).

Cells(Q, timestart).Value = Evaluate("""'"" & INDEX(TEXT(" & Cells(Q, timestart).Address(0, 0) & ",""dd/mmm/yyyy""),)")

Upvotes: 0

Views: 58

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

So this will ignore the blanks:

timestart = 1      '#Column 1
completeBad = 2    '#Column 2
timeSet = 24       '#Column 24
completeTime = 25  '#Column 25
lastrow = 1000
With Sheets(looping)
    With .Range(.Cells(2, timestart), .Cells(lastrow, timestart))
        .Value = Evaluate("""'"" & INDEX(IF(" & .Address(0, 0) & "<>"""",TEXT(" & .Address(0, 0) & ",""dd/mmm/yyyy""),""""),)")
    End With
    With .Range(.Cells(2, completeBad), .Cells(lastrow, completeBad))
        .Value = Evaluate("""'"" & INDEX(IF(" & .Address(0, 0) & "<>"""",TEXT(" & .Address(0, 0) & ",""dd/mmm/yyyy""),""""),)")
    End With
End With

Upvotes: 2

Related Questions