VBA Run-time error '1004'

[epactheactor]

Hello everyone.

I'm trying to finish the most complex macro I've ever done and have run into an error that is blowing my mind. I'm learning VBA so I would like to understand the error and not just get a fix, if that makes sense, so I don't make it again.

I get an the '1004' error on my Boo variable and I don't know why.

Sub RunScores

Dim Boo As String
Set XYZWorkbook = ActiveWorkbook
Boo = "A8"
'Goes through each subsegment
'If it is blank, it stops


Do While IsEmpty(Range(Boo).Value) = False
    Range(Boo).UnMerge
    Call FillInScores
    Range(Boo).Select
    Boo = Range(Boo).Offset(3, 0)
Loop

End Sub

Previously I wasn't using a loop and was renaming the Boo variable manually as well as well as repeating the code and it ran fine. But for what i am using it for, there could be 2 subsegments, or 100 so I really need to use a loop.

Thank you for any help.

 

[Fluff]

Try making this change

Boo = Range(Boo).Offset(3, 0)[COLOR=#ff0000].Address[/COLOR]

 

[steve the fish]

This line will just give you the value of what is in A11:

boo = Range(boo).Offset(3, 0)

so maybe use .Address

boo = Range(boo).Offset(3, 0).Address

 

[epactheactor]

For this, the error it gives is on the first "Do While IsEmpty(Range(Boo).Value) = False" As it shows there is no value in the Boo variable. Why would this be if I have declared it and given it a string input? The Offset line would be once it has completed the first run and checks for the next.

 

[epactheactor]

Never mind! I see it's giving the error after on second loop.

 

[Fluff]

I suspect that A11 is a blank cell

 

[epactheactor]

Yes and no. It's a weird workbook, the sort you really wish you could change to make more logical. Cells (A8:A10) are merged together. So I found that instead of (3,0) offset I really need to use (1,0) offset as (A11:A13) would have a value.

Overall I think I understand the error I was making. I wasn't fully telling it what cell to go to next to rename that variable.