Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '1004'
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '1004'
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. -- HTH... Jim Thomlinson "Caleb" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '1004'
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? Jim Thomlinson wrote: 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. -- HTH... Jim Thomlinson "Caleb" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '1004'
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... -- HTH... Jim Thomlinson "Caleb" wrote: 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? Jim Thomlinson wrote: 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. -- HTH... Jim Thomlinson "Caleb" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '1004'
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! On Oct 6, 2:17 pm, Jim Thomlinson wrote: 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... -- HTH... Jim Thomlinson "Caleb" wrote: 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? Jim Thomlinson wrote: 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. -- HTH... Jim Thomlinson "Caleb" wrote: 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.- Hide quoted text -- Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '1004'
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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error 1004 - General ODBC Error | Excel Programming | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Application Run Time Error 1004 and Stack Error | Excel Programming |