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*
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 All Please, please can someone explain how I may get the subtotal % to show on a pivot table report. Customer Name Product Type YTD Jul 2008 YTD Jul 2007 YTD- VARIANCE VARIANCE BRADFORDS-KEMPS (LEDBURY) DRAIN £15, 193.56 £9, 121.10 £6, 072.46 66.58% the appropriate answer. http: / / www.contextures.com / xlPivot10.html http: / / office.microsoft.com / en-us / excel / HP100963171033.aspx Thanks, but not what I'm looking for. However, I have solved the problem. . .if you set the customer subtotal to automatic, the calculated field subtotals correctly. % of Total In this example, the pivot table the appropriate answer. http: / / www.contextures.com / xlPivot10.html http: / / office.microsoft.com / en-us / excel / HP100963171033.aspx keywords: Microsoft Excel, report, Field, Show, Sum, Click, pivot table description: subtotal of
Bay chart error bars automation - constrained to averages? Excel
Hi, Over the past month I developed an Excel add-in (which I can't share in its entirely for legal reasons, unfortunately). The of survey panelists (there are multiple, varying numbers of observations / rows per respondent) - Uses the subtotal function to average values for that panelist on a number of "attributes", which are found Finally it creates error bars for each panelist's series of data * **The problem: VBA / Excel seems to limit my error bar choices to those which you could create by right std error, etc - but I have to find a way to make them reflect the variance in the data for each panelist's columns / attributes / bars. Any of you guys with Weight = xlThin .LineStyle = xlContinuous End With Selection.Interior.ColorIndex = xlNone ActiveChart.ChartArea.Select End Sub Excel Charting Discussions ActiveChart.SeriesCollection.NewSeries (1) Selection.ShapeRange.IncrementLeft (1) Selection.ShapeRange.IncrementTop (1) ActiveChart Range("E2:E5"), MinusValues: = Range("D2:D5") End With Cheers Andy - - Andy Pope, Microsoft MVP - Excel http: / / www.andypope.info Thanks Andy! I have not implemented it, but it looks like error, bars, automation, -, constrained, to, averages? description: Hi, Over the past month I developed an Excel add-in (which I can't share in its entirely for legal reasons, unfortunately). The
Pivot Table, Calculated Field Excel
I have a RowField ("Cycle") with two items ("Annual Plan", "Current Forecast") and a ColumnField items (Q1, Q2, Q3 and Q4). I want to add a third RowField item called "Variance" and have it be the difference between the Annual Plan and the Current Forecat for 2. How do i add the calculation formula via VB? Thanks for all your help. . .! Excel Programming Discussions PivotTables (1) Macro (1) VB (1) ColumnField (1) RowField (1) Lackawanna52 (1) Variance (1) Forecat (1) Use the macro recorder to record the steps as you do the following: Select the Cycle field On the Pivot toolbar, click PivotTables> Formulas> Calculated Item Type Variance as the name for the item Enter the formula, e.g.: = 'Annual Plan'- 'Current Forecast info. I will try it Debra, it worked great, but i'm not able to subtotal on the RowField ("Cycle") - see related post keywords: Pivot, Table, , Calculated, Field description: I have
LINEST with filtered data Excel
I am wondering if there is a way to use the LINEST function with data LINEST calculated for only the visible data. Hope this makes sense. Thanks in advance, David Excel Worksheet Discussions INTERCEPT (1) FORECAST (1) SUBTOTAL (1) LINEST (1) STEYX (1) SLOPE (1) RSQ (1) PredictedValue (1) The subtotal function can return stats like count, mean and variance on filtered data. So one way to get regression stats is to add a helper column by filling down in column F: = SUBTOTAL(3, A2) Then you can use, = SLOPE(E2:E9, IF(F2:F9, D2:D9)) and I understand what you are trying to do. Basically if the row is visible, the subtotal will produce a 1 (i.e. TRUE). Then you are using the slope() with nested
Populate worksheet with data that meets date range criteria Excel
Goal: By entering a beginning date and an ending date on Sheet2, I hope to few suggestions from similar posts. However, none quite work the way I need it to. Excel Worksheet Discussions SUBTOTAL (1) SUM (1) AutoFilter (1) Application (1) Excel (1) Range (1) Filter (1) Hi, I would consider putting the total calculation on the condition. If you use SUM functions on Sheet2, then on Sheet1 you probably would want SUBTOTAL(9, myrange). 9 tells the subtotal function to sum only visible cells, not the ones hidden by the filter. myrange is this is a bit detailed. However, I only want to save someone else with similar Excel skills some time trying to figure it out. I’m sure there is a hundred THE FILTERED DATA FOR MY REPORT: Within “FilteredReportâ€, in row 1063, I entered my SUBTOTAL formula to calculate only the visible data. The space between my SUBTOTAL formulas and the
What is the use of the numbers in Subtotal function i.e 1, 2, 3, 4, 5, 6, 7, 8, 9 = Subtotal(1, a2:a45) = Subtotal(2, a2:a45) = Subtotal(3, a2:a45) = Subtotal(4, a2:a45) = Subtotal(5, a2:a45) = Subtotal(6, a2:a45) = Subtotal(7, a2:a45) = Subtotal(8, a2:a45
Is there anywhere I can get a list of excel formulas and what they are for Excel Miscellaneous Discussions PivotTable (1) Excel 2007 (1) Excel 2003 (1) Office (1) NEGBINOMDIST (1) GETPIVOTDATA (1) For Excel 2007 - http: / / office.microsoft.com / en-us / excel / HP100791861033.aspx For Excel 2003 - http: / / office.microsoft.com / en-us / excel / HP052042111033.aspx Hi
Output form to excel with range ???? DataBase
I've read the code written by Mr Dev on the site Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C: \ MyFolder \ My Workbook.xls") Set wks = wkb.Worksheets 1) Shiro, here is a long post with lots of useful code for exporting to excel. I got this from our discussion group some time ago. I am posting this as example. Jeanette Cunningham You will need to do this in Access. You can manipulate the Excel object model from Access, but the syntax will be a little different that if you are actually in Excel. The code below is much more than you need, but you can use it to do need into your own code. The important part when using automation between Access and Excel is how you open and close a reference to Excel and how you refer to the Excel objects. If not done correctly, it can create problems. So, enjoy (and post back if
Discussions Excel (1) ExcelSalesAid (1) GuillettMicrosoft (1) SalesAid (1) Dguillett1 (1) I will certainly try to - - Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com keywords: excel description: excel