searching/grabbing info from one spreadsheet and placing it in another 1

[teakandme]

I have this piece of code that I would like to use to grab information from one spreadsheet to add to another. It is failing with an "Object variable or With block variable not set" I think it is cause I need code to open the second file....Anyone have any idea how I could do this?

Option Explicit
Sub mUpdate()
Dim vtest As Boolean, ctest As Boolean, return1 As Integer, rowcount As Integer, column As Integer, count As Integer
Dim compfile As Workbook, tcol As Integer, trow As Integer, filename As String

filename = "C:\Users\lthuynh\Documents\MH\Lab Data Collection_ final_aag2"
'Set compfile = Workbooks.Open(filename:=filename, Format:=5)


count = 0
rowcount = 3
column = 2
tcol = 1
trow = 1
ctest = True
vtest = True

return1 = MsgBox("Update the spreadsheet?", vbOKCancel, "Updating the spreadsheet")
If return1 = 1 Then
While vtest
If rowcount < 2106 Then

While ctest

If (sheet1.Columns.Cells(rowcount, column) = compfile.Worksheets("Lab Result-Order Code").Cells(trow, tcol).Value) Then

sheet1.Columns.Cells(rowcount, 4) = compfile.Worksheets("Lab Result-Order Code").Cells(trow, 5).Value
ctest = False
ElseIf rowcount > 3093 Then
ctest = False
Else
trow = trow + 1
End If
Wend
rowcount = rowcount + 1
Else
vtest = False
End If
Wend
End If

End Sub

 

[SkipVought]

Hi,

filename = "C:\Users\lthuynh\Documents\MH\Lab Data Collection_ final_aag2[b].xls[/b]"
Set compfile = Workbooks.Open(filename:=filename, Format:=5)

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[teakandme]

SkipVought,

Thank-you for your solution, it did the trick, but now I am getting an overflow error when the rowcount gets to 99. Do you have any ideas how to correct this?

 

[SkipVought]

Where is your EXIT from EITHER of your do loops?

rowcount is getting to WAAAAAAAYYYY more than 99!!!

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[combo]

When you go to leave the internal loop and go to next rowcount, I guess that the row reference (trow) should be reset.

combo