Alternative Dynamic VLookup on VBA

[wragostinho]

Hi, I'm trying to write a code to define an alternative VLOOKUP that works with closed files and in a dynamic way.

My problem is, although the code be very simples as you will see, I'm receiving a error message that I not able to solve.

Could someone help me?

--------------------------
Information about code (content of ranges):
Range("B4") = \\SERVER1\ENGINEERING\AREA\TESTS\VEHICLES\RACING\DUR\DAILY\ACTIVE\TEXT_RR.xlsx
$B$6 = 7
------------------------

Sub v_look_up()Dim iRow As Long

teste0 = InStr(1, Range("B4"), ".xlsm")
teste1 = InStr(1, Range("B4"), ".xlsx")
teste2 = InStr(1, Range("B4"), ".xls")
teste3 = InStrRev(Range("B4"), "")

If teste0 <> 0 Then
arquivo = Mid(Range("b4"), InStrRev(Range("B4"), "") + 1, InStr(1, Range("B4"), ".xlsm"))
Else
If teste1 <> 0 Then
arquivo = Mid(Range("b4"), InStrRev(Range("B4"), "") + 1, InStr(1, Range("B4"), ".xlsx"))
Else
If teste2 <> 0 Then
arquivo = Mid(Range("b4"), InStrRev(Range("B4"), "") + 1, InStr(1, Range("B4"), ".xls"))
End If
End If
End If

rangedebusca = "[" & arquivo & "]DAILY'!$A$14:$P$2500"
diretorio = Mid(Range("B4"), 1, InStrRev(Range("B4"), ""))

For iRow = 3 To 25
With Rows(iRow)
.Range("H3:H2503").Formula = "=VLOOKUP(" & .Range("E3").Address(False, True) & ";'" & diretorio & rangedebusca & ";$B$6;FALSE)"
End With
Next iRow

End Sub

-------------------
The error message is:

Run-time error '1004':
Application-defined or object-defined error
-------------------


A important information about the code is, if I copy the results of concatenation in the cell manually, everything works.

 

[Worf]

Welcome to the Board

What range is supposed to receive the formulas?

 

[wragostinho]

Welcome to the Board

What range is supposed to receive the formulas?

"H3:H25" - all rows in this range.

The information on code H2503 was wrong, sorry. I already changed it, I mean from H2503 to H25 and the error continues.

Thank's in advance.

 

[wragostinho]

I found the problem.


In this sentence,


"=" VLOOKUP ("& .Range (" E3 "). Address (False, True) &"; '"& directory & rangedearch &"; $ B $ 6; FALSE) "


I needed to replace all semicolon by comma, this way the VBA interpreter understood my code.


In this case I could stay using semicolon but I needed to use .FormulaLocal


Thank you again @Worf to try to help me.


Greetings.