vba vlookup error differente workbook

[mlcalves]

Hi people,

i'm trying do do a vlookup in vba with two differente files, but i get error:

run-time error '1004':

Unable to get the vlookup property of the worksheetfunction class


code:

Dim x As Integer
Dim procura As String

For x = 6 To 200
WB.Sheets("Ausências Previstas").Range("E" & x) = Application.WorksheetFunction.VLookup(WB.Sheets("Ausências Previstas").Range("B" & x), Presencas.Sheets("1").Range("B27:BF200"), 50, False)

Next

can anyone help me, please?

Thank you, regards

 

[Marc L]

Hi,​

​

enter manually a VLOOKUP formula in cell E6, try the same for cell E7 and once both work as expected,​

share each formula in this thread in order anyone can help without any useless loop …​

 

[mlcalves]

Hi,​

​

enter manually a VLOOKUP formula in cell E6, try the same for cell E7 and once both work as expected,​

share each formula in this thread in order anyone can help without any useless loop …​

Hi: the formula works:

=VLOOKUP(B6;'[Presenças_202111.xlsb]1'!$B$27:$BF$191;50;FALSE)
=VLOOKUP(B7;'[Presenças_202111.xlsb]1'!$B$27:$BF$191;50;FALSE)

my vba code is wrong?

 

[Marc L]

So try​

    With WB.Sheets("Ausências Previstas").Range("E6:E200")
        .Formula = "=VLOOKUP(B6;'[Presenças_202111.xlsb]1'!$B$27:$BF$191;50;FALSE)"
        .Formula = .Value2
    End With

 

[mlcalves]

With WB.Sheets("Ausências Previstas").Range("E6:E200") .Formula = "=VLOOKUP(B6;'[Presenças_202111.xlsb]1'!$B$27:$BF$191;50;FALSE)" .Formula = .Value2 End With

i need to user a for, because can be B6 to B200. doesn't work =/

 

[Marc L]

Yes if you need to slow down the execution you can use a loop allocating the formula cell by cell …​

 

[mlcalves]

​

Yes if you need to slow down the execution you can use a loop allocating the formula cell by cell …​

Sorry, but can you explain whats is wrong on this:

Dim x As Integer
Dim procura As String

For x = 6 To 200
WB.Sheets("Ausências Previstas").Range("E" & x) = Application.WorksheetFunction.VLookup(WB.Sheets("Ausências Previstas").Range("B" & x), Presencas.Sheets("1").Range("B27:BF200"), 50, False)

Next

this code should work, right?

thank you

 

[Marc L]

​

If it does not work so there is something obviously bad within !​

As you are using WorksheetFunction so the execution obviously crashes if the worksheet function returns any error …​

So try without this statement, just keep Application.​

​

But as obviously a loop is totally useless except to slow down your code …​

 

[mlcalves]

​

If it does not work so there is something obviously bad within !​

As you are using WorksheetFunction so the execution obviously crashes if the worksheet function returns any error …​

So try without this statement, just keep Application.​

​

But as obviously a loop is totally useless except to slow down your code …​

this works

For x = 6 To 200


    Dim lookFor As Range
    Dim srchRange As Range
    
    Set lookFor = WB.Sheets("Ausências Previstas").Range("B" & x)
    Set srchRange = Presencas.Sheets("1").Range("B27:BF200")
    
   WB.Sheets("Ausências Previstas").Range("E" & x) = Application.VLookup(lookFor, srchRange, 50, False)


Next

 

[Marc L]

​

Same result as post #4 …​