Consulting

Results 1 to 10 of 10

Thread: Solved: translate from Excel in VBA

  1. #1
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location

    Solved: translate from Excel in VBA

    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Typically that means you are missing a bracket, or have one in the wrong location.

    Also, please use linebreaks _
    in long lines of code
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    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?

  4. #4
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    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?

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,221
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    error 13. Type mismatch.

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

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,221
    Location
    Helping is difficult in VBA because of your different settings.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    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)

  9. #9
    VBAX Contributor
    Joined
    Dec 2009
    Location
    Sevastopol
    Posts
    150
    Location
    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
    Last edited by ZVI; 04-09-2010 at 07:14 PM.

  10. #10
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •