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

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

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

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

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