CODEWITHSUNDEEP
vbaexcel
Nicholas Humphrey
Nicholas Humphrey

Reputation: 1250

Strange occurence with VBA Evaluate and For loop

I'm using Evaluate() in a loop, and everything goes fine except the first evaluate in the whole loop.

RowBegin = 98 
For RowCount = RowBegin To RowBegin + 23   'Total 24 rows per site per day
    If (RowCount Mod 4 <> 1) Then
        Cells(RowCount, 6).Value = Evaluate(Cells(RowCount, 10).Value)
        Cells(RowCount, 7).Value = Evaluate(Cells(RowCount, 11).Value)
    End If
Next RowCount

Please note that 1 of the 4 rows is skipped because I have a formula there. All the Evaluate() works perfectly except for the first one:

Evaluate(Cells(98, 10).Value)

This one shows a #REF error. I double checked the formula and it is alright. In fact, even the next one within the same loop works:

Evaluate(Cells(98, 11).Value)

This is the formula I put in Cells(98,10):

='R:\20180220\[Filename.xlsb]Summary'!$D$2

And this is the formula I put in Cells(98,11):

='R:\20180220\[Filename.xlsb]Summary'!$D$3

However, if I modify the code to:

RowBegin = 98
For RowCount = RowBegin To RowBegin + 23   'Total 24 rows per site per day
    If (RowCount = RowBegin) Then
        Cells(RowBegin, 6).Value = Evaluate(Cells(RowBegin, 10).Value)
        Cells(RowBegin, 7).Value = Evaluate(Cells(RowBegin, 11).Value)
    End If
    If (RowCount Mod 4 <> 1) Then
        Cells(RowCount, 6).Value = Evaluate(Cells(RowCount, 10).Value)
        Cells(RowCount, 7).Value = Evaluate(Cells(RowCount, 11).Value)
    End If
Next RowCount

Then everything works.

Stranger is, this also works:

RowBegin = 98
For RowCount = RowBegin To RowBegin + 23   'Total 24 rows per site per day
    If (RowCount = RowBegin) Then
        Cells(RowBegin, 7).Value = Evaluate(Cells(RowBegin, 11).Value)
    End If
    If (RowCount Mod 4 <> 1) Then
        Cells(RowCount, 6).Value = Evaluate(Cells(RowCount, 10).Value)
        Cells(RowCount, 7).Value = Evaluate(Cells(RowCount, 11).Value)
    End If
Next RowCount

Please note that in the first IF, it actually does not touch Cells(RowCount, 6)!

I'm scratching my head because I think the first piece of code and the second one is exactly the same (because when RowCount = 98 it is not skipped anyway). Does it have anything to do with the loop or the Mod operator?

Upvotes: 1

Views: 322

Answers (1)

dashnick
dashnick

Reputation: 2060

I think the first time it kind of opens the external reference - but not in time - and throws an error, and then from then on it is okay. So the reason the IF works and not the ELSEIF is because when you use the IF it redoes it again, while the ELSEIF it doesnt. This explains all that weird behavior. Just keep a dummy line in there to open it, like:

RowBegin = 98 
Cells(RowBegin, 6).Value = Evaluate(Cells(RowBegin, 10).Value) ' Dummy
For RowCount = RowBegin To RowBegin + 23   'Total 24 rows per site per day
    If (RowCount Mod 4 <> 1) Then
        Cells(RowCount, 6).Value = Evaluate(Cells(RowCount, 10).Value)
        Cells(RowCount, 7).Value = Evaluate(Cells(RowCount, 11).Value)
    End If
Next RowCount

Upvotes: 1

Related Questions