Macro VLOOKUP

[SH Harbour]

Hi

I am attempting to look up data from another worksheet in the same work book, the data can be anything from 1 line up to 9584 lines, is there any way to tidy the following and also if data is only in A2 to A100 that it doesn't produce #N/A in the remaining empty rows from line 101 to 9584?

Sub VLOOKUPAnotherSheet()


For Each cell In Range("$A$2:$A$9584")
cell.Offset(0, 1) = Application.Vlookup(cell, Sheets("CommodityCodes").Range("$A$2:$I$9584"), 6, False)
Next

For Each cell In Range("A2:A9584")
cell.Offset(0, 2) = Application.Vlookup(cell, Sheets("CommodityCodes").Range("A2:I9584"), 7, False)
Next

For Each cell In Range("A2:A9584")
cell.Offset(0, 3) = Application.Vlookup(cell, Sheets("CommodityCodes").Range("A2:I9584"), 8, False)
Next

For Each cell In Range("A2:A9584")
cell.Offset(0, 4) = Application.Vlookup(cell, Sheets("CommodityCodes").Range("A2:I9584"), 9, False)
Next

End Sub


Thanks

 

[coulombevin]

Hi SH Harbour,

why do you use VBA to do a lookup in the sheet directly and drag it down? Also, could you provide the name of the actual sheet and if you want to extract the value in the cell or place the formula in the cell?

bests regards,

Vincent

 

[SH Harbour]

Hi SH Harbour,

why do you use VBA to do a lookup in the sheet directly and drag it down? Also, could you provide the name of the actual sheet and if you want to extract the value in the cell or place the formula in the cell?

bests regards,

Vincent

Hi Vincent

The sheet with variables is called Tariff, the variables are all in column A and this column can change, there is standing data in the sheet named CommodityCodes so I need to quickly be able to enter data in the tariff sheet column A and then the Macro to find the standing data that relates to that cell and then but that data in the tariff sheet in the corresponding columns, B,C,D & E

Thanks

 

[coulombevin]

Hi SH Harbour,

here's the VBA snippet that I would use: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

Sub VLOOKUPAnotherSheet()
    Dim toFind As Variant, foundCell As Range
    Dim i As Integer
    
    With Sheets("Tariff")
        i = 2
        'for each row from 2 to 9584
        For i = 2 To 9584
            'get the value to find
            toFind = .Range("A" & i).Value
            ' make sure that you don't check for nothing
            If toFind <> "" Then
                'check if it is found in column A
                Set foundCell = Sheets("CommodityCodes").Range("A:A").Find(What:=toFind)
                
                'if it is found in the column A (it is not empty)
                If Not foundCell Is Nothing Then
                    'set the column B,C,D,E of sheet Tarif with the column F,G,H,I of CommodityCodes
                    .Range("B" & i) = Sheets("CommodityCodes").Range("F" & foundCell.Row).Value
                    .Range("C" & i) = Sheets("CommodityCodes").Range("G" & foundCell.Row).Value
                    .Range("D" & i) = Sheets("CommodityCodes").Range("H" & foundCell.Row).Value
                    .Range("E" & i) = Sheets("CommodityCodes").Range("I" & foundCell.Row).Value
                Else
                    'remove Else and next 4 row if  you dont want to delete value from Tarif if it is not found
                    .Range("B" & i) = ""
                    .Range("C" & i) = ""
                    .Range("D" & i) = ""
                    .Range("E" & i) = ""
                End If
            End If
        Next
    End With
End Sub

Currently, this code will remove data from column B,C,D,E of the sheets Tariff if the value in A is not found in sheets CommodityCodes. If you want to keep invalid datas, you should remove the lines with the comment that tell you to do so.

Bests regards,

Vincent

 

[Fuji]

try

Sub test()
    Dim x&
    With Sheets("Tariff")
        x = .Evaluate(Replace("max(if(#<>"""",row(#)))", "#", .UsedRange.Address))
        With .Range("b2:b" & x).Resize(, 4)
            .Formula2 = "=iferror(vlookup($a2,commoditycodes!$a$2:$i$9854,column(f1),false),"""")"
            .Value = .Value
        End With
    End With
End Sub

 

[SH Harbour]

Hi SH Harbour,

here's the VBA snippet that I would use: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

Sub VLOOKUPAnotherSheet()
    Dim toFind As Variant, foundCell As Range
    Dim i As Integer
   
    With Sheets("Tariff")
        i = 2
        'for each row from 2 to 9584
        For i = 2 To 9584
            'get the value to find
            toFind = .Range("A" & i).Value
            ' make sure that you don't check for nothing
            If toFind <> "" Then
                'check if it is found in column A
                Set foundCell = Sheets("CommodityCodes").Range("A:A").Find(What:=toFind)
               
                'if it is found in the column A (it is not empty)
                If Not foundCell Is Nothing Then
                    'set the column B,C,D,E of sheet Tarif with the column F,G,H,I of CommodityCodes
                    .Range("B" & i) = Sheets("CommodityCodes").Range("F" & foundCell.Row).Value
                    .Range("C" & i) = Sheets("CommodityCodes").Range("G" & foundCell.Row).Value
                    .Range("D" & i) = Sheets("CommodityCodes").Range("H" & foundCell.Row).Value
                    .Range("E" & i) = Sheets("CommodityCodes").Range("I" & foundCell.Row).Value
                Else
                    'remove Else and next 4 row if  you dont want to delete value from Tarif if it is not found
                    .Range("B" & i) = ""
                    .Range("C" & i) = ""
                    .Range("D" & i) = ""
                    .Range("E" & i) = ""
                End If
            End If
        Next
    End With
End Sub

Currently, this code will remove data from column B,C,D,E of the sheets Tariff if the value in A is not found in sheets CommodityCodes. If you want to keep invalid datas, you should remove the lines with the comment that tell you to do so.

Bests regards,

Vincent

Thanks Vincent, that worked perfectly and significantly faster than the previous method I had.

Thanks

 

[coulombevin]

Hi SH Harbour,

Your'e welcome, if that's all, could you set the previous post as solution?

Thanks,

Vincent