Convert Vlookup to VBA Error Message

[barim]

Hello,
I have this code below and I am trying to convert Vlookup function into VBA. I have a little database called “MyDatabase" in a separate workbook, first sheet. My lookup starts comparison of column “B” from “WorkingFile” to “MyDatabase” of column “B”. Position of these columns is fixed, so there is no need to make them dynamic. Once there is a match, corresponding value in column “E” (Offset(0,3) from “MyDatabase” file should be copied to corresponding column “E” (Offset(0,3) in destination file which is “WorkingFile”.
When I ran this code I’ve got “Compile error: Statement invalid outside Type block” and it points to lrow1 variable that I set up as integer. I tried to change it to Long and it didn’t work. Please let me know what is wrong with my codes. Data type that I am trying to look up is numbers.

Thanks a lot!

Sub MyLookup()
    'Define variables
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    lrow1 As Integer
    lrow2 As Integer
    c1 As Range
    c2 As Range
    
    'Define workbooks
    Set wb1 = Workbooks("MyDatabase.xlsx")
    Set wb2 = Workbooks("WorkingFile.xlsx")
    
    'Define last row of each column in each workbook for vlookup
    lrow1 = wb1.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    lrow2 = wb2.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    
    'Loop through column B of MyDatabase and test if there is anything matching with column B in WorkingFile
    'Once found value in MyDatabase copy value from 3rd column to 3rd column on WorkingFile
    For Each c1 In lrow1
        If c1.Value <> "" Then
            For Each c2 In lrow2
                If c1.Value = c2.Value Then
                    c1.Offset(0, 3).Value = c2.Offset(0, 3).Value
                    
                    Exit For
                    
                    End If
                Next c2
                    End If
                Next c1
            
    
End Sub

 

[sunny102]

Hi

You have to open the workbooks first, then only you can use their content
change Set wb1 = Workbooks("MyDatabase.xlsx")
to
Set wb1 = Workbooks.Open("MyDatabase.xlsx")

Hello,
I have this code below and I am trying to convert Vlookup function into VBA. I have a little database called “MyDatabase" in a separate workbook, first sheet. My lookup starts comparison of column “B” from “WorkingFile” to “MyDatabase” of column “B”. Position of these columns is fixed, so there is no need to make them dynamic. Once there is a match, corresponding value in column “E” (Offset(0,3) from “MyDatabase” file should be copied to corresponding column “E” (Offset(0,3) in destination file which is “WorkingFile”.
When I ran this code I’ve got “Compile error: Statement invalid outside Type block” and it points to lrow1 variable that I set up as integer. I tried to change it to Long and it didn’t work. Please let me know what is wrong with my codes. Data type that I am trying to look up is numbers.

Thanks a lot!

Sub MyLookup()
    'Define variables
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    lrow1 As Integer
    lrow2 As Integer
    c1 As Range
    c2 As Range
    
    'Define workbooks
    Set wb1 = Workbooks("MyDatabase.xlsx")
    Set wb2 = Workbooks("WorkingFile.xlsx")
    
    'Define last row of each column in each workbook for vlookup
    lrow1 = wb1.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    lrow2 = wb2.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    
    'Loop through column B of MyDatabase and test if there is anything matching with column B in WorkingFile
    'Once found value in MyDatabase copy value from 3rd column to 3rd column on WorkingFile
    For Each c1 In lrow1
        If c1.Value <> "" Then
            For Each c2 In lrow2
                If c1.Value = c2.Value Then
                    c1.Offset(0, 3).Value = c2.Offset(0, 3).Value
                    
                    Exit For
                    
                    End If
                Next c2
                    End If
                Next c1
            
    
End Sub

 

[barim]

I think I found where mistake was. I forgot to declare some variables as Dim. Now I encountered another error message. It points to variable lrow1 in my first for loop. "For Each may only iterate over a collection object or an array". I need to replace this for each.

 

[barim]

I changed lrow1 and lrow2 as Variant and it opened my workbooks that I needed. Now I have different error message: Object error 424. When I debug lrow1 reads correct number of data rows, but c1 is "Nothing". Do I need to set c1 as object? This is only my counter for for loop.

 

[sunny102]

What is c1 and c2 doing in your code? Are they counters? and you have not set them anything that's why they are empty

I changed lrow1 and lrow2 as Variant and it opened my workbooks that I needed. Now I have different error message: Object error 424. When I debug lrow1 reads correct number of data rows, but c1 is "Nothing". Do I need to set c1 as object? This is only my counter for for loop.

 

[barim]

I've made some changes to this code. Error message points to this line: c1.Offset(0, 3).Value = c2.Offset(0, 3).Value. How do I test this data and once when it is matched I transfer it to another workbook?

Sub MyLookup()
    'Define variables
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim lrow1 As Variant
    Dim lrow2 As Variant
    Dim c1 As Variant
    Dim c2 As Variant
    
    'Define workbooks
    Set wb1 = Workbooks.Open("MyDatabase.xlsx")
    Set wb2 = Workbooks.Open("WorkingFile.xlsx")
     
    'Define last row of each column in each workbook for vlookup
    lrow1 = wb1.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    lrow2 = wb2.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    
    'Loop through column B of MyDatabase and test if there is anything matching with column B in WorkingFile
    'Once found value in MyDatabase copy value from 3rd column to 3rd column on WorkingFile
        
    For c1 = 2 To lrow1
        For c2 = 2 To lrow2
                If c1.Value = c2.Value Then
                    c1.Offset(0, 3).Value = c2.Offset(0, 3).Value
                    
                    Exit For
                    
                    End If
                Next c2
                    
                Next c1
            
    
End Sub