Microsoft Excel - Excel VBA: If statement using formula to find the variance in the subtotal row.

Asked By Grant Zaunick on 21-Jun-12 11:05 AM
Earn up to 30 extra points for answering this tough question.
Hi, I'm new to Excel VB somewhat.  I rarely use excel, and could use a few pointers.

I'm trying to create a macro that runs a formula on certain rows.  The rows I want the formula to run on have some distinct characteristics, but I cannot make the macro automate the process.  Here is my logic...

If C* is null then 
  H* =(G*-F*)/F*
Else 
  Do Nothing
End If

The * stands for whichever row applies to the If statement.
I want it to run this function all the way down the spreadsheet until the end of the data.  I usually work with databases so the logic I use doesn't seem to work.

I had previously tried to run this function, but didn't seem to do what I wanted because it would really run slow because it would try to apply the function to ever single row in the document down to row 1048576 ..
=If(IsBlank(C6),(G6-F6)/F6, DO NOTHING)

Any help would be appreciated. 

Thanks,

Grant
Pichart Y. replied to Grant Zaunick on 21-Jun-12 09:13 PM
Hi Grant, Seems not complicate, but what does cause the problem? We should define the range of data then loop through to check the condition the perform the function. How many rows does your data is? It will be more easy to understand your requirement and exact support if you attach your file here. Pichart Y.
wally eye replied to Grant Zaunick on 21-Jun-12 09:27 PM
If you can't do it with a formula:

=if(c2="",(g2-f2)/f2,"")

a bit of VBA:

public sub UpdateValues

  dim arrCalcs        as variant
  dim arrResults()     as variant

  dim intStart         as integer
  dim lngLastRow    as long
  dim lngRow        as long

  intstart = 2   '  Starting data row
  on error resume next
  lnglastrow = activesheet.columns(3).find(What:="*", After:=[C1], _
    SearchOrder:=xlbyrows, SearchDirection:=xlprevious).row
  if err > 0 then
    lnglastrow = 0
  endif
  on error goto 0
  if lnglastrow >= intstart then
   arrcalcs = activesheet.cells(intstart,3).resize(lnglastrow-intstart + 1, 5)
   redim arrresults(lbound(arrcalcs) to ubound(arrcalcs))
   for lngrow = lbound(arrcalcs) to ubound(arrcalcs)
    if arrcalcs(lngrow,1) ="" then
      arrresults(lngrow) = (arrcalcs(lngrow, 5) - arrcalcs(lngrow,4))/arrcalcs(lngrow,4)
      else
      arrresults(lngrow) = ""
      endif
    next lngrow
    activesheet.cells(intStart,7).resize(lnglastrow-intstart + 1,1) = application.transpose(arrresults)
  endif

end sub

Just change intstart to match the row with your starting data, and run it with the desired sheet active.
Donald Ross replied to Grant Zaunick on 22-Jun-12 12:40 AM
=if(C*="",(G*-F*)/F*,"")  where "" means nothing either =equals nothing or do nothing. and the formula would be in H* based on the way you asked your question.

once you get it right in the first row you can drag down to populate the rest.

Don

Grant Zaunick replied to wally eye on 22-Jun-12 07:56 AM
Perfect.  This is what I was looking for.  I knew it was something like that.

Thank you very much!!!

Grant
Pichart Y. replied to Grant Zaunick on 24-Jun-12 10:58 PM
Hi Grant,

If your requirement is only this...then...you can use this short & simple like this

Sub calBycond()
For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
CalRow = cell.Row
If cell = "" Then Range("H" & CalRow) = (Range("G" & CalRow) - Range("F" & CalRow)) / Range("F" & CalRow)
Next
End Sub


hope this help.

Pichart y
Pichart Y. replied to Grant Zaunick on 25-Jun-12 10:37 AM
Hi Grant try this.,...

--> Vba_CalWithCond.zip

pichart Y