vba - Range loop provided Jan1900 date error

Question

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""),)")

**#Column 1/2/24/25** is not actually part of my code. I included this for readability for everyone. If there is a typo it is probably just my horrible ninja typing skills. — IT_User, Apr 01 '16 at 18:06

Scott Craner · Accepted Answer · 2016-04-01T19:15:17.323

2

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

edited Apr 01 '16 at 19:15

answered Apr 01 '16 at 18:16

Scott Craner

148,073
10
49
81

You sir, are amazing! Thank you very much. Again :) – IT_User Apr 01 '16 at 18:24
1

nice but just a precaution... See [This](http://stackoverflow.com/questions/18962890/2-ways-for-clearcontents-on-vba-excel-but-1-work-fine-why) – Siddharth Rout Apr 01 '16 at 18:50
@SiddharthRout I agree the OP should assign the parentage to the range objects. Without seeing the rest of the code it was difficult to make any assumptions. – Scott Craner Apr 01 '16 at 19:05
@SiddharthRout I have my entire module inside of a for loop that sets the `ActiveWorkbook.Sheets(looping).Activate` – IT_User Apr 01 '16 at 19:12
@SiddharthRout not 100% sure if that resolves the issue you are talking about but that is how I am calling what worksheet I am on. – IT_User Apr 01 '16 at 19:13
1

@bluerojo. You may want to see [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) :) – Siddharth Rout Apr 01 '16 at 19:13
@bluerojo then you should simply use another With Block I will edit my answer to show. – Scott Craner Apr 01 '16 at 19:13
@ScottCraner Just to let you know. This piece of code is only a small chunk of the 500+ lines for this one module (5 in all right now). It resolves some heavy parsing of data and formatting issues, as well as calculates new fields based off of status fields etc. – IT_User Apr 01 '16 at 19:17
@SiddharthRout that is very interesting. I will have to try to implement this code into my modules. – IT_User Apr 01 '16 at 19:19
@bluerojo I added only for information. I hope you have split up your code so that any duplicated routines are in their own. Also, I agree with Siddarth, Avoid the `.Select` or `.Activate`. It slows down the code and is not needed in most cases. – Scott Craner Apr 01 '16 at 19:20
@ScottCraner Honestly my entire code could/should definitely be refactored (if time permits). As I started out with no knowledge of the full extent of the problem and really no VBA experience (Unix/Linux man though), my code is probably horrendous for anyone to look at with a moderate level of VBA experience. – IT_User Apr 01 '16 at 19:28

vba - Range loop provided Jan1900 date error

1 Answers1