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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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! (y)
 
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
172
Joe4
Joe4
nathanzumbaugh
  • Question
Add blank cells below pasted data from sheet to sheet using VBA-have working code, just need add row syntax
Replies
0
Views
74
nathanzumbaugh
nathanzumbaugh
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
654
USNA91
U
A
  • Solved
VBA Help!
Replies
2
Views
184
AusNewb
A
I
  • Solved
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
375
ItalianPlatinum
I

Forum statistics

Threads
1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

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