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
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