# VBA code for complex search • ### 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
• 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.