Add AVERAGE Formulas To Cells Using Variables In Macro Code

Apr 15, 2008

I am trying to have the formula =( SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Put into cells through vba. What I did to get the formula is typed it into an excel cell to find the average of a group of cells that do contain blank cells. The formula brought out the proper results. So all I did is put the formula into vba and changed the appropriate parts. The range will not be the same of course, but there is what I have.

ActiveCell.Formula = "=(SUMIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "," & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & "))/(COUNTIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "))"

When I show a msgbox for ActiveCell.Formula (Msgbox activecell.formula), it shows me the formula as above - =(SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Except instead of the s:ranges, it shows $L2:$L125 (which is correct). The quotes do show up around the criteria in both the sumif and countif. I keep receiving an error. I put a msgbox err.description & ", " err.number dialog in. The error comes up as ", 0" (no quotes).

View 2 Replies


ADVERTISEMENT

Macro Code That Takes Average Of Next 24 Cells Down

Feb 4, 2013

I have this one query though in regards to loops.

I am trying to create a macro that can take the average of the the first 24 cells within a sheet, place the answer onto a cell in the next sheet (e.g. sheet2 in cell A1), then go back to the previous sheet, take the average of the next 24 cells within the sheet and paste the average of this new set in A2. I want to create a loop that will do this 365 times.

I have only managed to create the following code, however its only obtainning the average for the first set of 24 cells starting from B6 in sheet 1. I dont know how to use offsets that well....

VB:
Sub Oval1_Click()
For i = 1 To 365
Sheets("H1 - Riser Turret pressure").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[2]C:R[25]C)"
Range("B4").Offset(1, 0).Select
Next i
End Sub

View 6 Replies View Related

Add Vlookup, With Variables, To Cell To Reference Another Workbook Via Macro Code

May 21, 2008

I am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1

The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.

View 9 Replies View Related

Add Nest Formula/Function To Cell With Variables Via Macro Code

May 22, 2008

I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

View 6 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Average Of Range In Macro Code

Sep 30, 2006

I have to make VBA codes to get the average of two ranges. The problem is I always get 0 value. The value of each cells came from the formula that's why I use .TEXT. Here is the code.

iAveragePrep = WorksheetFunction.Average(Range(Cells(275, 4).Text), Cells(275, 5).Text)

View 8 Replies View Related

Code Which Converts Cells With Formulas To A New Formula

Jul 2, 2009

Someone here wrote up this code for me which converts cells with formulas to a new formula (It adds a division to the formula in the cell). It works great with one problem. If the code is rerun multiple times it doesn't check if there is a division added already and just keeps adding and adding to existing formula. For the last our I tried to add a check to the code. Something along the lines "If cell already has "/H2" inserted" skip to next cell. But nothing is working.

Here is the code I'm working with:

Sub Macro2()

Dim MyRange As Range, NewFormaula As String, Delim As String
Dim Cell As Range
Dim MyString As String
Dim Format As Range
Dim ws As Worksheet

View 9 Replies View Related

Formulas Added From Macro Code Don't Update

Mar 12, 2008

I used VBA to write a number of formulas into various cells in a workbook. After the VBA is done running, I change the values in the cells that are referenced by the cells I wrote formulas in. The formulas do not update after I enter this new information. My calculation is set to automatic, and F9 doesn't help.

View 3 Replies View Related

Macro Code To Add Formulas To Reference Different Sheets

Aug 8, 2008

How do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.

View 6 Replies View Related

Vb Macro Code For Calculating Sum And Average Of Rainfall For Each Year?

Mar 5, 2014

I need to calculate SUM and AVERAGE of rainfall for each and every year separately and must be displayed separately in a separate column. For your easy understanding, I have done manually and attached the excel sheet.

View 6 Replies View Related

Formulas In Offset: Run It The Cells Remain Blank But The Code Is Above In The Fx

Apr 26, 2006

s1. Offset(1, 2).Range("K4:K464").Formula = "=IF(ISNA(VLOOKUP(C2,Sheet2!$A$2:$B$1063,2,FALSE)),"""",(VLOOKUP(C2,Sheet2!$A$2:$B$1063,2,FALSE)))"

How do you get code like above to work in Offset? When i run it the cells remain blank but the code is above in the fx.

View 9 Replies View Related

Enter Array Formulas To Range Macro Code

Apr 23, 2008

I'm trying to enter a series of formulas referencing the first cell of each row.

With Range("A40")
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).FormulaR1C1 = "= COUNTIF(Details!R2C2:R65536C2,RC1)"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 2).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC[-2])*('Details'!R2C11:R65536C11=RC1))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 4).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 5).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 7).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 8).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"
End With

While this code works for the first formula, the following 4 are arrays, and for some reason, will only reference the first A40 cell.

View 9 Replies View Related

Macro For Populating Cells Using Variables From Another Column

Jun 20, 2006

I need to create a macro, where the contents of a particular cell are dependent upon summing values based on a word desription from another column. For instance, I would like cell E10 to include a numerical value from cell D5, but ONLY if cell C5 is populated with the word "trust" as opposed to "equity". Conversely, I would also have a macro in, say, cell E11 that would do the same for the word "equity" in column C. I would like the macro to hold for the entirety of different words in column C.

View 6 Replies View Related

Macro Code To Average Multiple List Of Varying Column Ranges

Apr 4, 2009

I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:

Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

View 9 Replies View Related

Use Variables In VBA Formulas?

Oct 9, 2011

In VBA, I just want to use an object within a formula that represents rows within a range of cells.

Here's what I mean. I create an variable named "additup":

additup = 2 'This value represents that row number I will want in the following messed up formula:

ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-7]),RC[-7]/MAX(range(P(additup:Padditup+6000")),"""")"

Above, the formula checks if the cell that is 7 columns to the left is a number, and if so, divide it by the max value within the range (Col P, Row= additup) : (Col P, Row= additup+6000).

Clearly my problem is VBA syntax.

View 2 Replies View Related

Variables In Sum Formulas?

Jan 3, 2012

For our monthly report we would like to make a sum formular where the end column is a variable, so it can be updated one time instead of updating every formular. When I try a text formular it doesn't calculate but only show the text string. ="=sum(b5:"& a1 & "5)" so I can enter c in cell a1 for 2 comumn/month summation.

View 5 Replies View Related

Formulas For Gradiants And Variables

Nov 29, 2008

I am looking for formulas to work out what is on the worksheet attached

View 2 Replies View Related

Calculate Daily Average Of The Several Variables

Jun 3, 2009

I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "date-time" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.

View 3 Replies View Related

Defining Average Range With Variables?

Apr 5, 2012

It's just meant to work through a long list of data blocks defined by column 8 changing. At that point I want to calculate the average of the first 20 entries in columns 12, 13, 14, 15, 16, 17 in that particular block.

I can achieve the sort and calculate the block start rows OK, just can't get the average definition right.

Code:
Option Base 1
Dim c(6)
Sub calc_averages()

[Code]....

View 2 Replies View Related

Macro To Clear Cells With Numbers But Not Cells With Formulas

Jan 22, 2013

Macro to clear cells with numbers but no cells with formulas with in this macro:

Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub

The range is where the cells with numbers need to be cleared but not the ones with formulas.

View 2 Replies View Related

Using Variables In Formulas Being Assigned To Cell Value

Dec 10, 2013

What I'm trying to do is use a variable NumberOfRows (which is defined as an integer) in this following code, but the problem I get is that the NumberOfRows isn't being inserted.

Let's assume that NumberOfRows = 15.

What I would expect to be in cell B1 is =sum(H5:H15), but what is getting placed there is =sum(H5:HNumberOfRows) which of course doesn't give me anything. I'm sure this is an easy syntax thing, but I just can't seem to get the right combination.

Range("B1").Formula = "=Sum(H5:H&NumberOfRows)"

View 2 Replies View Related

Graphing Formulas Woth Continuous Variables

Feb 10, 2009

I need to graph a function of SIN(X), I did it by writing my x-values in one column with small steps between each value and then calculating the sin(x) value in the column next to it and then graphing the two columns. But this raquires a lot of space on the spreadsheat and it would be convenient to be able to write it on the form y=sin(x). having x defined as a continuous variable. and then plotting the graph

View 2 Replies View Related

Search/Replace Variables Workbook Formulas

Aug 25, 2006

I am using heavily formula based modelling workbooks and I need to know how to search for variables (inputs) in these workbooks as they contain many thousand rows and it would take ages to go through line by line looking for these variables

View 3 Replies View Related

Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...

View 11 Replies View Related

Macro That Insert Formulas Into Cells

Mar 19, 2008

Macros that will insert formulas into cells, and I've been able to modify the macros to insert additional (simpler) formulas well enough on my own. But I can't seem to find the pattern of where to put the quotation marks! Here's the macro code that will enter my formula, including the formula (and the portion of code that the debugger highlights):

r.Offset(, 1).FormulaR1C1 = _
"=VLOOKUP(rc[-1]"",""'Blend List'""!""A2"":""E250"",5,FALSE)"
And here's the code in it's entirety:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Columns("d")) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
For Each r In Intersect(Target, Columns("d"))
If r.Row 1 Then
If Not IsEmpty(r.Value) Then...........

View 9 Replies View Related

Macro To Hide Formulas In Cells?

Jul 17, 2009

HI you know any macro to hide formulas in cells?

View 9 Replies View Related

Macro For Average Of Values - Dynamic Range Of Cells

Aug 1, 2013

I need to find average of the values , the count of the cells will be dynamic (may be 5 or even 200).

View 2 Replies View Related

VBA Macro To Average Non-Contiguous But Regular Interval Cells?

Jun 8, 2013

I have a worksheet with 35,136 rows of data. I want to calculate the average of every 4th cell in the same column. Is there a way to create a macro to select every 4th cell in the average formula? If so, can I then substitute min and max for average?

View 1 Replies View Related

Vlookup/Macro: Regions In Sheet2, Average Of Next 6 Cells

Mar 2, 2010

I am trying to write a macro do the folowing

1. Vlookup Regions in Sheet2 (eg. AP-Asia/Pasific)
2. Check which row they are in
3. then take the average of next 6 cells (AVERAGE 1,310, 6,744 etc..)

View 9 Replies View Related

VBA Macro: Clear All Constant Cells In Range That Don't Have Formulas

Jun 22, 2009

is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

from another thread (which is excellent!)

- SHG's recommendation of using a named range, for example:

Range("Inputs").ClearContents

Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved