• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Converting If Then statemnts to VBA

Hi all, I need to convert the following If then statements used for conditional formatting to VBA format so they can be used to determine if the formatting was applied. I would also need another formula that combines the two, as I need formatting that would cover both of these formats. Many thanks for any assistance you all can provide.

=IF(AND(NOT(ISBLANK(I29)),NOT(ISBLANK(G29)),NOT(ISBLANK(E29)),NOT(SUM(G29+I29)=E29)),TRUE,FALSE)

=IF(AND(OR(((F8-I8)/I8>0.2),((F8-I8)/I8<-0.2)),NOT(ISBLANK(I8)),NOT(ISBLANK(F8))),TRUE,FALSE)
0
shoaibs
Asked:
shoaibs
3 Solutions
 
Rory ArchibaldCommented:
If Evaluate("IF(AND(NOT(ISBLANK(I29)),NOT(ISBLANK(G29)),NOT(ISBLANK(E29)),NOT(SUM(G29+I29)=E29)),TRUE,FALSE)") THen

Open in new window


and:
If Evaluate("IF(AND(OR(((F8-I8)/I8>0.2),((F8-I8)/I8<-0.2)),NOT(ISBLANK(I8)),NOT(ISBLANK(F8))),TRUE,FALSE)") Then

Open in new window


Didn't really follow the last part.
0
 
HainKurtSr. System AnalystCommented:
=IF(AND(NOT(ISBLANK(I29)),NOT(ISBLANK(G29)),NOT(ISBLANK(E29)),NOT(SUM(G29+I29)=E29)),TRUE,FALSE)

dim result as boolean

if (i29<>"" and G29 <>"" and E29<>"") then
if  cint(G29)+cint(I29)=cint(E29) then
  result = true
else
  result = false
end if
0
 
dlmilleCommented:
That works, but if your ranges are changes, or you're moving down a column trying to do this formula in a loop, you'll need to have those ranges handy and rebuild the suggested EVALUATE concatenating that string together with your changing addresses, or do this:

Dim colI as string, colG as string, colE as string, colF as string
dim colIaddr as range, colGaddr as range, colEaddr as string, colFaddr as string

be sure to set these ranges as you go through your loop (assuming you are in a loop), then:

For row = 1 to whatever '(assuming going down row by row)

  colI = range("I" & row).address
  colG = range("G" & row).address
  colE = range("E" & row).address
  colF = range("F" & row).address

  If Evaluate("IF(AND(NOT(ISBLANK(" & colI & ")),NOT(ISBLANK(" & colG & ")),NOT(ISBLANK(" & colE & ")),NOT(SUM(" & colG & "+" & colI & ")=" & colE & ")),TRUE,FALSE)") Then
          your code.
..
..

'and

If Evaluate("IF(AND(OR((("& colF & "-" & colI & ")/" & colI & ">0.2),((" & colF & "-" & colI & ")/" & colI & "<-0.2)),NOT(ISBLANK(" & colI & )),NOT(ISBLANK(" & colF & "))),TRUE,FALSE)") Then
      your code

...
...
next row

Hope this helps!

Dave

0
 
Rory ArchibaldCommented:
Not really - if those formulas are being used for CF, you can pull them straight out of the FormatConditions for each cell and evaluate them directly. :)
0
 
shoaibsAuthor Commented:
@Rorya- thank you so much for that simple solution- I spent all afternoon tearing my hair out trying to convert those statements directly to VBA

@dlmille- thanks for your help as well- very good to know.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now