Solved vba criteria if statement based on certain text

October 24, 2018 at 17:59:08
Specs: Windows 7
I have the following routine that I would like to clean-up, it works fine but I think it can be written better.
Basically I want to know how I could make it so if cell D12 contains the letters
"FHA" that it would then evaluate if cell D6 is greater than .90 if so then the value of D16 is .85 otherwise
if D12 contains the letters "FHA" and D6 is less than .90 then the value of D16 is .80.

Here is my code that works but as I add products that have the letters "FHA" my if statement is going to
get bigger and bigger. I am sure there is a way to base my if statement by evaluating if the letters "FHA"
are contained in the cell regardless of what other wording is in the cell:

Sub Calc_MI()
'ActiveSheet.Unprotect Password:="Mortgage1"
If Sheets("Main").Range("D12").Value = "FHA" And Sheets("Main").Range("D6").Value > 0.9 Or 
Sheets("Main").Range("D12").Value = "HFA Broward FHA Grant" And Sheets("Main").Range("D6").Value > 0.9 
Then Sheets("Main").Range("D16").Value = 0.85 
Else If Sheets("Main").Range("D6").Value < 0.8001 Or 
Sheets("Main").Range("D12").Value = "VA" Or Sheets("Main").Range("D12").Value = "Jumbo" Then
Sheets("Main").Range("D16").Value = ""

Else
If Sheets("Main").Range("G14").Value > 0.45 Then
   Sheets("Main").Range("D16").Value = (Sheets("Closing Costs").Range("BP100").Value + 
Sheets("Closing Costs").Range("BP101").Value + Sheets("Closing Costs").Range("BP102").Value)
Else
   Sheets("Main").Range("D16").Value = (Sheets("Closing Costs").Range("BP100").Value + 
Sheets("Closing Costs").Range("BP102").Value)
End If
End If
End If
If Sheets("Main").Range("D12").Value = "FHA" And Sheets("Main").Range("D6").Value < 0.91 Or 
Sheets("Main").Range("D12").Value = "HFA Broward FHA Grant" And Sheets("Main").Range("D6").Value < 0.91 
Then
Sheets("Main").Range("D16").Value = 0.8
'ActiveSheet.Protect Password:="Mortgage1"
End If
End Sub

message edited by mecerrato


See More: vba criteria if statement based on certain text

Reply ↓  Report •

#1
October 24, 2018 at 19:37:53
✔ Best Answer
Test if "FHA" is anywhere in the cell.

If Sheets("Main").Range("D12") Like "*FHA*" Then...

or

Return the starting position of "FHA" in the cell. If it's starting position is >0, then it must be in the cell.

If InStr(Sheets("Main").Range("D12"), "FHA") > 0 Then ...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 25, 2018 at 06:52:46
Thanks DerbyDad03, that worked great (the first one made more sense to use). Here is the code for anyone else that may DAGS this issue :-):

Sub Calc_MI()
'ActiveSheet.Unprotect Password:="Mortgage1"
If Sheets("Main").Range("D12") Like "*FHA*" And Sheets("Main").Range("D6").Value > 0.9 Then
Sheets("Main").Range("D16").Value = 0.85
Else
If Sheets("Main").Range("D12") Like "*FHA*" And Sheets("Main").Range("D6").Value < 0.91 Then
Sheets("Main").Range("D16").Value = 0.8
Else
If Sheets("Main").Range("D6").Value < 0.8001 Or Sheets("Main").Range("D12").Value = "VA" Or 
Sheets("Main").Range("D12").Value = "Jumbo" Then
Sheets("Main").Range("D16").Value = ""
Else
If Sheets("Main").Range("G14").Value > 0.45 Then
   Sheets("Main").Range("D16").Value = (Sheets("Closing Costs").Range("BP100").Value + 
Sheets("Closing Costs").Range("BP101").Value + Sheets("Closing Costs").Range("BP102").Value)
Else
   Sheets("Main").Range("D16").Value = (Sheets("Closing Costs").Range("BP100").Value + 
Sheets("Closing Costs").Range("BP102").Value)
End If
End If
End If
End If
'ActiveSheet.Protect Password:="Mortgage1"
'End If
End Sub

message edited by mecerrato


Reply ↓  Report •

#3
October 25, 2018 at 08:17:56
I agree that Like is the better method in this case.

I just wanted to introduce you to the InStr method, perhaps for future reference. Maybe someday you'll need to split one of those FHA strings or extract some text. That's where the InStr method might come in handy.

That said, I'm a little confused by your code.

Wouldn't this do the same thing for your "FHA" section? I'm not sure that there is a need to check for both values (FHA and D6) multiple times. It seems like you only need to check for FHA once and then deal with D6 (or not).

If Sheets("Main").Range("D12") Like "*FHA*" Then
 If Sheets("Main").Range("D6").Value > 0.9 Then
   Sheets("Main").Range("D16").Value = 0.85
Else
   Sheets("Main").Range("D16").Value = 0.8
 End If
End If

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 25, 2018 at 17:17:02
Thanks I will definitely refer to this post if I ever need the InStr example. I tried using your suggestion but it did not work. It would properly function until it gets to this part of the code it does not function correctly, it calculates it as if wasn't FHA at all.

If Sheets("Main").Range("D6").Value < 0.8001 Or Sheets("Main").Range("D12").Value = "VA" Or 
Sheets("Main").Range("D12").Value = "Jumbo" Then
Sheets("Main").Range("D16").Value = ""

message edited by mecerrato


Reply ↓  Report •

#5
October 25, 2018 at 18:11:41
It may be just a matter of an End If or an Else in the wrong place. All I did was test (and post) the part that checks for FHA so that you could see a different way of performing your 2 tests.

Did you Single Step through the code to see what path it follows? I'm guessing that you just need to rearrange some lines of code, perhaps moving all the rest of your code above my final End If. All I was doing was creating a snippet that had the correct number of If's and End If's so that it (just the snippet) ran correctly.

Have I ever referred you to this tutorial?


http://www.computing.net/howtos/sho...

message edited by DerbyDad03


Reply ↓  Report •

#6
October 25, 2018 at 18:50:50
You haven't but I just skimmed through it and it is very much needed, I will read thoroughly. I did single step through it and was able to do exactly what you said and relocated some of the code and it fixed it, thanks again for the knowledge transfer :-)

Reply ↓  Report •

Ask Question