Consulting

Results 1 to 3 of 3

Thread: Solved: Index/Match Formular to VBA conversion

  1. #1

    Solved: Index/Match Formular to VBA conversion

    Hey I'm having trouble converting a formular to VBA.

    Currently I have:

     
    Dim FormularE, Bus, WhatToFind as string
    Dim E as single
     
    FormularE = "INDEX(E$2:E$100,MATCH(1,(A$2:A$100=" & Bus & ")*(D$2:D$100=" & WhatToFind & "),0))"
     
    With PanelSheet
          E = .Evaluate(formularE)
    End With
    Which is supposed to look up the value in column E in the same row a that Bus and WhatToFind are in.

    When I first tried to evaluate it, I was getting E = Error 2015.

    I thought this might be because Bus can sometimes be a string containing a space, so I changed the value of Bus so that there were no spaces and re-ran the formular. I am now getting E = Error 2042.

    Any idea what I have done wrong?

    Additional information that may or may not be useful:

    1. There is data validatation in column D, allowing only certain strings to be entered.

    2. The table containing the data does not extend to row 100, but may expand, hence the larger ranges

    3. There is definately a match

    4. I would like ideally to be able to define Bus as strings with gaps in them, but am happy to lose this ability if it will make things simpler.

    PS I know I've spelt Formula wrong - may get around to changing it at some point

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    If Bus is a string

    [vba]

    Dim FormularE, Bus, WhatToFind as string
    Dim E as single

    FormularE = "INDEX(E$2:E$100,MATCH(1,(A$2:A$100=""" & Bus & """)*(D$2$100=" & WhatToFind & "),0))"

    With PanelSheet
    E = .Evaluate(formularE)
    End With
    [/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

  3. #3
    You're a star xld, I know it was something like that, but couldn't work outt the right location of the extra quotes. thanks

Posting Permissions

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