Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 1004 with a twist
Help pls. Am getting an error 1004 Application defined or Object defined
error. I have looked at the other posts with the same error but I cannot seem to be able to correct my problem. The Do While line is being flagged as the problem. Do I need to select the worksheet first? If so what is the right code for doing that with the ThisWorkbook object Dim bk2 As Workbook Set bk2 = Workbooks.Open(Filename:=fileToOpen) With ThisWorkbook.Sheets("fdbpre") RowCount = 1 Do While .Range("CU" & RowCount) < " " MON = .Range("CU" & RowCount) With bk2.Sheets("Sheet1") Set c = .Columns("A").Find(what:=MON, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then .Range("EG" & RowCount) = .Range("CB" & RowCount) * c.Offset(0, 1) End If RowCount = 1 = RowCount = 1 + 1 Loop End With -- Lindy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 1004 with a twist
You are going through the loop the 1st time and failing the 2nd time due to
the increment statement of the RowCount RowCount = 1 = RowCount = 1 + 1 The above line will produce a TRUE or FLASE response which is not a valid number. When TRUE or False is put into the Range you get Do While .Range("CUTrue) < " " which is not a valid cell reference "Lindy" wrote: Help pls. Am getting an error 1004 Application defined or Object defined error. I have looked at the other posts with the same error but I cannot seem to be able to correct my problem. The Do While line is being flagged as the problem. Do I need to select the worksheet first? If so what is the right code for doing that with the ThisWorkbook object Dim bk2 As Workbook Set bk2 = Workbooks.Open(Filename:=fileToOpen) With ThisWorkbook.Sheets("fdbpre") RowCount = 1 Do While .Range("CU" & RowCount) < " " MON = .Range("CU" & RowCount) With bk2.Sheets("Sheet1") Set c = .Columns("A").Find(what:=MON, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then .Range("EG" & RowCount) = .Range("CB" & RowCount) * c.Offset(0, 1) End If RowCount = 1 = RowCount = 1 + 1 Loop End With -- Lindy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 1004 with a twist
I have altered the statement to
RowCount = RowCount + 1 but am still getting the same error. Sorry I am rather new to vba and am a little lost. -- Lindy "Joel" wrote: You are going through the loop the 1st time and failing the 2nd time due to the increment statement of the RowCount RowCount = 1 = RowCount = 1 + 1 The above line will produce a TRUE or FLASE response which is not a valid number. When TRUE or False is put into the Range you get Do While .Range("CUTrue) < " " which is not a valid cell reference "Lindy" wrote: Help pls. Am getting an error 1004 Application defined or Object defined error. I have looked at the other posts with the same error but I cannot seem to be able to correct my problem. The Do While line is being flagged as the problem. Do I need to select the worksheet first? If so what is the right code for doing that with the ThisWorkbook object Dim bk2 As Workbook Set bk2 = Workbooks.Open(Filename:=fileToOpen) With ThisWorkbook.Sheets("fdbpre") RowCount = 1 Do While .Range("CU" & RowCount) < " " MON = .Range("CU" & RowCount) With bk2.Sheets("Sheet1") Set c = .Columns("A").Find(what:=MON, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then .Range("EG" & RowCount) = .Range("CB" & RowCount) * c.Offset(0, 1) End If RowCount = 1 = RowCount = 1 + 1 Loop End With -- Lindy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 1004 with a twist
You now have a different reason for the same error on the same statement
Do While .Range("CU" & RowCount) < " " Looking closer at your posted code is looks like you have a blnk space between the two double quotes. There should not be any space. Remove it. The code is suppose to look for the first empty cell to end the code. You are looking for a cell withh one space. The code is not finding any cell with one space so the code keeps on running to the last row 65537 and getting an error .Range("CU65537") is not a valid cell. the rows stop in excel at 65536 "Lindy" wrote: I have altered the statement to RowCount = RowCount + 1 but am still getting the same error. Sorry I am rather new to vba and am a little lost. -- Lindy "Joel" wrote: You are going through the loop the 1st time and failing the 2nd time due to the increment statement of the RowCount RowCount = 1 = RowCount = 1 + 1 The above line will produce a TRUE or FLASE response which is not a valid number. When TRUE or False is put into the Range you get Do While .Range("CUTrue) < " " which is not a valid cell reference "Lindy" wrote: Help pls. Am getting an error 1004 Application defined or Object defined error. I have looked at the other posts with the same error but I cannot seem to be able to correct my problem. The Do While line is being flagged as the problem. Do I need to select the worksheet first? If so what is the right code for doing that with the ThisWorkbook object Dim bk2 As Workbook Set bk2 = Workbooks.Open(Filename:=fileToOpen) With ThisWorkbook.Sheets("fdbpre") RowCount = 1 Do While .Range("CU" & RowCount) < " " MON = .Range("CU" & RowCount) With bk2.Sheets("Sheet1") Set c = .Columns("A").Find(what:=MON, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then .Range("EG" & RowCount) = .Range("CB" & RowCount) * c.Offset(0, 1) End If RowCount = 1 = RowCount = 1 + 1 Loop End With -- Lindy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 1004 with a twist
And if it may contain a space (or multiple spaces):
Do While trim(.Range("CU" & RowCount)) < "" Joel wrote: You now have a different reason for the same error on the same statement Do While .Range("CU" & RowCount) < " " Looking closer at your posted code is looks like you have a blnk space between the two double quotes. There should not be any space. Remove it. The code is suppose to look for the first empty cell to end the code. You are looking for a cell withh one space. The code is not finding any cell with one space so the code keeps on running to the last row 65537 and getting an error .Range("CU65537") is not a valid cell. the rows stop in excel at 65536 "Lindy" wrote: I have altered the statement to RowCount = RowCount + 1 but am still getting the same error. Sorry I am rather new to vba and am a little lost. -- Lindy "Joel" wrote: You are going through the loop the 1st time and failing the 2nd time due to the increment statement of the RowCount RowCount = 1 = RowCount = 1 + 1 The above line will produce a TRUE or FLASE response which is not a valid number. When TRUE or False is put into the Range you get Do While .Range("CUTrue) < " " which is not a valid cell reference "Lindy" wrote: Help pls. Am getting an error 1004 Application defined or Object defined error. I have looked at the other posts with the same error but I cannot seem to be able to correct my problem. The Do While line is being flagged as the problem. Do I need to select the worksheet first? If so what is the right code for doing that with the ThisWorkbook object Dim bk2 As Workbook Set bk2 = Workbooks.Open(Filename:=fileToOpen) With ThisWorkbook.Sheets("fdbpre") RowCount = 1 Do While .Range("CU" & RowCount) < " " MON = .Range("CU" & RowCount) With bk2.Sheets("Sheet1") Set c = .Columns("A").Find(what:=MON, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then .Range("EG" & RowCount) = .Range("CB" & RowCount) * c.Offset(0, 1) End If RowCount = 1 = RowCount = 1 + 1 Loop End With -- Lindy -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error when cell A1 is not active and xlInsideVertical border formatthrowing error 1004 | Excel Programming | |||
#DIV/0! Error-Another Twist, assistance please? | Excel Worksheet Functions | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Application error 1004 with a twist. | Excel Programming |