Run Time Error '1004'

[Caleb]

I am getting a run time error '1004' (Application defined or object
defined error) and the debugger seems to be highlighting a relatively
simple piece of the code. Below is the section of code that is causing
a problem.

Do Until (Count = 40)

If Trade.FormulaR1C1 = "" Then
Set Trade = Trade.Offset(2, 0)
Count = Count + 1
Else: Workbooks(file2).Activate
Set tag2 = Range("A1")
Do Until tag2.FormulaR1C1 = foldername
Set tag2 = tag2.Offset(1, 0) <----- This is the problem
Loop
Set YTD2 = tag2.Offset(0, Column)
YTD2.Copy
Monitor.Activate
YTD3.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Set Trade = Trade.Offset(2, 0)
Set YTD3 = Trade.Offset(1, 2)
Count = Count + 1
End If

Loop

If this is not enough information to get help, I apologize. I've
tested the macro by tweaking the code and it opens the second work book
fine, and will select a range just fine, it seems to be specifically
the "offset" that is causing the problem. I am truly at a loss here,
this seems to be such an innocuous piece of code to be causing a
problem.

Thanks in advance.

 

[Guest]

On possible would be that the exit condition for the loop is never met
meaning that you offset right off of the sheet...

Do Until tag2.FormulaR1C1 = foldername 'Is the conditon ever met?

What is the point to the FormulaR1C1 as opposed to the value? Are you really
looking for a formula. That might be a good place to start.

 

[Guest]

It look like you are moving tag2 downward. it is possible that it goes down
to the end and can't go down anymore.

Try this in the loop before the problem occurs:
if tags.Row >= Rows.Count then msgbox "Oh Oh, this is the end. " +
cstr(tags.Row)

 

[Caleb]

That seems like it may be on the right track actually. As for using
FormulaR1C1, I am using it because I am looking at a text cell (the
cells contain names of different folders). I was under the impression
that you needed to use FormulaR1C1 when it was text if you wanted to be
able to ignore capitalization. I'm teaching myself VBA though, so I
fully admit that I really don't have any idea what I'm talking about.
What is the difference in formula and value exactly?

 

[Guest]

Formula returns the formula if there is one and value returns what you see in
the cell. If there is no formula then formula returns the value...

 

[Caleb]

Gotcha, thanks. The problem was there was no way to break out of the
loop if it went through all the cells and didn't find what it was
looking for. Easy to fix now that you guys pointed it out. Thanks!