Help Converting VBA Code To A Nested Cell Formula

C

CaliKidd

Board Regular
Joined
Feb 16, 2011
Messages
173
I'm not very good with nested formulas when there are a lot levels and would appreciate some help from an expert.

How would I convert the following blocks of VBA code into nested IF formulas? There are two variations shown below that I am seeking solutions for. For each variation, my goal is to replace all of the code with a formula I can place in cell K4. My data starts off on row 4, but I want to be able to copy the formula down column K to the end of my data set, however, the cell reference on Sheet 2 is fixed and should not change.

1. The code first determines the value (letter grade) of cell C4 of the "Sheet 1" worksheet. It then performs three different logic tests to determine if a "Pass" or "Fail" result should be returned. A "Pass" only results if ALL three tests are passed, otherwise a "Fail" results.

I know the code is horrendous, but I wrote it out quickly in the form of nested If-Then-Else statements with the hope that it may be easier for someone to convert into a nested =IF formula.

Code: 
If Worksheet("Sheet 1").Range("C4") = "A" then
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B208") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") ="Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "B" then
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B209") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "C" then
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B210") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "D" then
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B211") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "F" then
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B212") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]End If

2. The second block of VBA code is slightly more complicated as it contains an OR function. There is an AND function that binds the first two tests but an OR function for the third test. Once again, the Sheet 2 cell reference is fixed and should not change.
Code: 
If Worksheet("Sheet 1").Range("C4") = "A" then
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B208") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "B" then
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B209") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "C" then
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B210") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "D" then
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B211") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "F" then
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B212") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]End If
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
For the first condition, put this formula in K4 (on 'Sheet 1')...

Code: 
=IF(C4="","",IF(AND(J4<=M4,J4>=L4,P4<=OFFSET('Sheet 2'!B208,CODE(C4)-64-(C4="F"),0)),"Pass","Fail"))
For the second condition, try putting this formula in K4 (on 'Sheet 1')...

Code: 
=IF(C4="","",IF(OR(AND(J4<=M4,J4>=L4),P4<=OFFSET('Sheet 2'!B208,CODE(C4)-64-(C4="F"),0)),"Pass","Fail"))
 
Last edited:
Upvote 0
1.)
Code: 
=IF(ISERROR(MATCH(C4,{"A","B","C","D","F"},0)),"",
  IF(AND(J4<=M4,J4>=L4,P4<=INDEX(Sheet2!$B$208:$B$212,MATCH(C4,{"A","B","C","D","F"},0))),"Pass","Fail"))

2.)
Code: 
=IF(ISERROR(MATCH(C4,{"A","B","C","D","F"},0)),"",
  IF(OR(AND(J4<=M4,J4>=L4),P4<=INDEX(Sheet2!$B$208:$B$212,MATCH(C4,{"A","B","C","D","F"},0))),"Pass","Fail"))
 
Upvote 0
Wow, thanks, guys. Very impressive! I am always amazed at the expertise on this board.

While both solutions are appreciated very much, I've decided to go with AlphaFrog's approach since it has some error checking built-in and is a bit more understandable to my novice eyes.

I wanted to ask for some final help coming up with a third variation for added flexibility. The formula would use the following logic:
- find out the letter grade (e.g., A, B, C, D, or F)
- determine what the logic rules should be used for that letter grade. In other words, it would see if each letter should use the first formula (variation #1) or the second formula (variation #2).

In an effort to describe what I am asking, the VBA code would be as follows:
Code: 
If Worksheet("Sheet 1").Range("C4") = "A" then
[INDENT]If Worksheet("Sheet 2").Range("B220") =  True then 'TRUE means use the variation #1 formula
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B208") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") ="Fail"
[/INDENT]End If
[/INDENT]Else 'if not True, then use the variation #2 formula
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B208") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]End If 
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "B" then
[INDENT]If Worksheet("Sheet 2").Range("B221") =  True then 'TRUE means use the variation #1 formula
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B209") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") ="Fail"
[/INDENT]End If
[/INDENT]Else 'if not True, then use the variation #2 formula
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B209") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]End If 
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "C" then
[INDENT]If Worksheet("Sheet 2").Range("B222") =  True then 'TRUE means use the variation #1 formula
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B210") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") ="Fail"
[/INDENT]End If
[/INDENT]Else 'if not True, then use the variation #2 formula
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B210") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]End If 
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "D" then
[INDENT]If Worksheet("Sheet 2").Range("B223") =  True then 'TRUE means use the variation #1 formula
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B211") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") ="Fail"
[/INDENT]End If
[/INDENT]Else 'if not True, then use the variation #2 formula
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B211") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
[/INDENT]End If
[/INDENT]End If
[/INDENT]Elseif Worksheet("Sheet 1").Range("C4") = "F" then
[INDENT]If Worksheet("Sheet 2").Range("B224") =  True then 'TRUE means use the variation #1 formula
[INDENT]If Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") And Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4") And Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B212") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") ="Fail"
[/INDENT]End If
[/INDENT]Else 'if not True, then use the variation #2 formula
[INDENT]If (Worksheet("Sheet 1").Range ("J4") <= Worksheet("Sheet 1").Range ("M4") AND (Worksheet("Sheet 1").Range("J4") >= Worksheet("Sheet 1").Range("L4")) OR Worksheet("Sheet 1").Range("P4") < = Worksheet("Sheet 2").Range("B212") Then
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Pass"
[/INDENT]Else
[INDENT]Worksheet("Sheet 1").Range ("K4") = "Fail"
End If
[/INDENT][/INDENT]End If 
[/INDENT]End If
Thanks in advance for your support and expertise. It will be amazing to see if all that code can be converted to a one-line formula!
 
Upvote 0
Not tested.

Code: 
=IF(ISERROR(MATCH(C4,{"A","B","C","D","F"},0)),"",
  IF(INDEX(Sheet2!$B$220:$B$224,MATCH(C4,{"A","B","C","D","F"},0)),
  IF(AND(J4<=M4,J4>=L4,P4<=INDEX(Sheet2!$B$208:$B$212,MATCH(C4,{"A","B","C","D","F"},0))),"Pass","Fail"),
  IF(OR(AND(J4<=M4,J4>=L4),P4<=INDEX(Sheet2!$B$208:$B$212,MATCH(C4,{"A","B","C","D","F"},0))),"Pass","Fail")))
 
Upvote 0
AWESOME! Works like a charm. :pray:

I don't know how you do it, but I am impressed! I am going to have to dissect this monster to better understand the genius behind it.

It is remarkable that an entire block of VBA code can be compressed into a single-line of well-crafted, nested formula functions! :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

bluefeather8989
  • Solved
VBA add 1 to cell without select
Replies
3
Views
284
Joe4
Joe4
I
  • Solved
Simplify my working code please
Replies
7
Views
179
ipbr21054
I
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
414
mumps
mumps
KlausW
  • Locked
  • Question Question
VBA code to move picture from on sheets to another sheet
Replies
1
Views
42
Fluff
Fluff
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
333
bdt
B

Forum statistics

Threads
1,224,574
Messages
6,179,626
Members
452,933
Latest member
patv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top