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
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*
  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. 


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:


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
  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)
      arrresults(lngrow) = ""
    next lngrow
    activesheet.cells(intStart,7).resize(lnglastrow-intstart + 1,1) = application.transpose(arrresults)

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.


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!!!

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

If your requirement is only 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)
End Sub

hope this help.

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


pichart Y