none
VBA code for complex search RRS feed

  • Question

  • Hello, 

    I have been working on an excel project and basically i am running into a limitation with how many nested functions I can have. What I am trying to do is pull a value from a specified location depending on four criteria. So for example if E14 = EU, F14 = H1, G14 = TS, and H14<I14 then (((I14-H14)*'H1'!L3)+H14) if E14 = AB, F14 = H1, G14 = TS, and H14<I14 then (((I14-H14)*'H1'!L4)+H14). In these cases I am pulling the value on sheet H1 cell L3, and L4 and doing math to it If you could show me how to do this in vba that would be great. 

    Monday, August 11, 2014 5:05 PM

All replies

  • It's not clear what you want to do. I assume you mean nested If's but you've only show two.  Does your logic a series of If..  Else, what does  "then (((I14-H14)*'H1'!L3)+H14)" mean, is that part of a condition you want to check or a calculation you want to return as the overall result, but to what? 
    Monday, August 11, 2014 9:09 PM
    Moderator
  • Thanks, for your messages. It was nested and I was exceeding the allowed nested and character limits so if you can imagine a string of equations 8000 chars long. Anyway I figured a way to do what I needed using index and match that is now only 400 chars.
    Tuesday, August 12, 2014 1:00 AM
  • Hi Dev,

    >>It was nested and I was exceeding the allowed nested and character limits so if you can imagine a string of equations 8000 chars long. <<

    Did you mean the value of cell based on the other cell? If yes, you can use the VBA code to set the value layer upon layer. Here is a example:

    Here is the VBA code:

    Sub SetValueA1()
    Range("A1") = 5
    
    End Sub
    
    Sub setValueB1()
    If Range("A1") > 0 Then
        Range("B1") = Range("A1") - 1
    Else
         Range("B1") = Range("A1") + 1
    End If
    End Sub
    
    Sub setValueC1()
    If Range("B1") > 0 Then
        Range("C1") = Range("B1") - 1
    Else
         Range("C1") = Range("B1") + 1
    End If
    End Sub
    
    Sub test()
    SetValueA1
    setValueB1
    setValueC1
    End Sub
    

    If I misunderstood, please feel free to let me know.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 19, 2014 10:01 AM
    Moderator