Solved: translate from Excel in VBA

[white_flag]

Good morning

how can this to be translate in VBA:

[VBA]
=VLOOKUP(I5;INDIRECT(LOOKUP(LEFT(I5;1);{"R";"F"};{"Rockwool";"Foamglass"})) ;2)
[/VBA]
[VBA]
Cells(8, strLastCol + 1).Value = "=VLOOKUP(" & ActiveCell.Offset(3, 0).Address(True, False) & ";INDIRECT(LOOKUP(LEFT(" & ActiveCell.Offset(3, 0).Address(True, False) & ";1);{"R";"F"};{"Rockwool";"Foamglass"}));2)"
[/VBA]

I have the Error: End of statement.

thank you

[mdmackillop]

Typically that means you are missing a bracket, or have one in the wrong location.

Also, please use linebreaks _
in long lines of code

[white_flag]

If I put the formula that work in excel in VBA, it tell me the same:
Error End of statement. I have all the bracket in correct place otherwise will not work in excel. What can it be?

[white_flag]

this string can not be translate:

[VBA]{"R";"F"},{"Rockwool";"Foamglass"}[/VBA]
without this I have no error. but I need that. It is an solution?

[Bob Phillips]

Try this

[vba]

Cells(8, strLastCol + 1).Value = ActiveSheet.Evaluate("=VLOOKUP(" & ActiveCell.Offset(3, 0).Address(True, False) & _
";INDIRECT(LOOKUP(LEFT(" & ActiveCell.Offset(3, 0).Address(True, False) & _
";1);{""R"";""F""};{""Rockwool"";""Foamglass""}));2)")
[/vba]

[white_flag]

error 13. Type mismatch.

I try also """ + R + """, " & R & " nothing

[Bob Phillips]

Helping is difficult in VBA because of your different settings.

[white_flag]

correct. the regional settings are different.
I have the same situation from 0.6 and 0,6. Wen I write 0.6 excel transform this in 0,6 (and it is an number). wen excel take 0.6 via VBA put 0,6 but mark with exclamation point "Number stored as text"

for my problem solution was ";" to be put it "," and between ""R"";"F"

[VBA]
Cells(8, strLastCol + 1).Value = "=VLOOKUP(" & ActiveCell.Offset(3, 0).Address(True, False) & _
",INDIRECT(LOOKUP(LEFT(" & ActiveCell.Offset(3, 0).Address(True, False) & _
",1),{""R"";""F""},{""Rockwool"";""Foamglass""})),2)"

[/VBA]
it is an possibility to trick excel on number case? 0.6 become 0,6 without exclamation point. If I try the way around 0,6 it is acting the same (the same exclamation point)

[ZVI]

Hi white_flag,
You can try also these examples:

' Original formula: =VLOOKUP(I5;INDIRECT(LOOKUP(LEFT(I5;1);{"R";"F"};{"Rockwool";"Foamglass"}));2)
' Warning1: approximate match is chosen in your VLOOKUP formula because the last argument is omitted
' Warning2: LOOKUP returs "Foamglass" for I5 values equal to "S","T","U","V"... and so on

' Write array formula
Sub WriteArrayFormulla1()
  ActiveCell.FormulaArray = "=VLOOKUP(I5,INDIRECT(LOOKUP(LEFT(I5,1),{""R"",""F""},{""Rockwool"",""Foamglass""})),2)"
End Sub

' Write array formula, using value from 3 rows down to the active cell
Sub WriteArrayFormulla2()
  Dim a As String
  With ActiveCell
    a = .Offset(3, 0).Address(1, 0)
    .FormulaArray = "=VLOOKUP(" & a & ",INDIRECT(LOOKUP(LEFT(" & a & ",1),{""R"",""F""},{""Rockwool"",""Foamglass""})),2)"
  End With
End Sub

' Evaluate method, issues of both Warnings are fixed
Sub VbaEvaluate()
  Dim a$, b$
  With ActiveCell.Offset(3, 0)
    a = .Address
    Select Case UCase(Left(.Value, 1))
      Case "R":   b = "Rockwool"
      Case "F":   b = "Foamglass"
      Case Else:  MsgBox a & vbTab & .Value, , "Not found": Exit Sub
    End Select
    MsgBox Evaluate("VLOOKUP(" & a & "," & b & ",2,0)")
  End With
End Sub

Regards,
Vladimir

[white_flag]

thx, Vladimir

for the first Warning I just add the 0 to the end of the formula.
but for the second Warning you are correct. thx for your solution.

have an nice weekend