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)

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.