PDA

View Full Version : Solved: translate from Excel in VBA



white_flag
04-09-2010, 12:13 AM
Good morning

how can this to be translate in VBA:


=VLOOKUP(I5;INDIRECT(LOOKUP(LEFT(I5;1);{"R";"F"};{"Rockwool";"Foamglass"}));2)


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)"


I have the Error: End of statement.

thank you

mdmackillop
04-09-2010, 12:33 AM
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
04-09-2010, 01:08 AM
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
04-09-2010, 01:23 AM
this string can not be translate:

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

Bob Phillips
04-09-2010, 01:38 AM
Try this



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)")

white_flag
04-09-2010, 01:45 AM
error 13. Type mismatch.

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

Bob Phillips
04-09-2010, 01:58 AM
Helping is difficult in VBA because of your different settings.

white_flag
04-09-2010, 02:24 AM
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"


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)"


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
04-09-2010, 06:59 PM
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
04-10-2010, 01:04 AM
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 :)