View Single Post
  #1 (permalink)  
Old September 6th, 2007, 10:37 AM
Aprile Aprile is offline
Registered User
Join Date: Sep 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Aprile
Default transferring/copying from spreadsheet to another

I have this code that I would like to use to get info from one spreadsheet to add to another, but there's always this "Object variable or With block variable not set."

Any ideas as to how to remedy this? Here's the code:

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
            trow = trow + 1
            End If
        rowcount = rowcount + 1
        vtest = False
        End If
End If

End Sub
Thanks in advance.

Kind regards,

Aprile - Access Repair Tool
Reply With Quote