# 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]....

## Defining A Range With Variables And Cells Function

Aug 25, 2009

I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.

## Code: Defining Variables

Sep 15, 2008

I'm trying to develop a work spreadsheet template that is to be summarised every fortnight. Our fortnights here go Thurs-Thurs. So I figured I would start with a base date (thurs - Sept 4th) and then have the spreadsheet display the true fortnight ending upon loading. Ultimately this fortnight ending will be used to copy and paste the template information into a summary spreadsheet but for now I am trying to get this intial part working. It's linked to a command button and my attempt at code is below:

Private Sub CommandButton1_Click()
Dim Base As Date
Dim Now As Date
Dim Delta As Integer
Dim No_of_Fortnights!
Dim Rounded As Integer
Dim Fract!
Dim Fort_week_end As Date

(Note the following 8 lines are not the problem, they just open the main sheet. The problem is the eights lines starting with the word 'set')

Sheets("Technical Inquiry Form").Activate
Sheets("Technical Inquiry Form").Range("b3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("Technical Inquiry Form").Range("b3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Technical Inquiry Form").Activate

## Defining Rows As Variables

May 25, 2006

What the code does is that it runs in a Master workbook, and searches for XLS files in a defined folder. It opens a file, count, closes the workbook and takes the tallied number of sheets and places it in the master file, column A being File Names and column B as Number of sheets. What I need is for the code to take the file name from column A, run it and then place sheet counts in Column B and then move down the columns until all the counting is done. Currently I am only able to define one row using the range function and i'm at a loss.

'Get file names and enter into Master
Public Sub ShowFolderList()
Dim wb As Workbook
Dim ws As Worksheet
ChDrive "C"
ChDir "C:Documents and SettingsTest Folder"
outrow = 2
Filess = Dir("*.xls")
While Not Filess = ""
Cells(outrow, 1).Value = Filess
outrow = outrow + 1
Filess = Dir()
Wend.......................

## Defining Variables In Separate Module

Jun 18, 2008

I started plugging away at VB a little over a month a go and i feel i've learnt alot but can see just how limited my knowledge is. how to define all my variables in one module that then will feed into the code on other modules and forms in the workbook where required. For example, my spreadsheet has approx 30 different variables that are called upon by a variety of subs. How do i define all these in one place and then just call on the relevant ones when required rather then having to redefine them for every new sub.

## Defining Variables Using Variables

Apr 27, 2006

I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...

Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................

## IF Formula - Defining And Range

Jan 22, 2010

I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!

1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under

blanks or zeros = unknown

## Defining Cell Or Range

Jun 11, 2009

I'm having an issue trying to locate a certain value in a cell.
If it is a number, say 50, then my code works, if its a word though
then it doesnt work. How can I get this to search with a list of words rather then numbers?

Sub alpha()
Dim cell As Range
For Each cell In Range("A2", ["N27"])
If cell.Value = Beff Then cell.Interior.ColorIndex = 6 'doesnt work
If cell.Value = 50 Then cell.Interior.ColorIndex = 6 'does work
Next cell
Range("A2", ["N27"]).Font.Bold = True
Range("A2").CurrentRegion.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub

## User Defining A Cell Range

Mar 31, 2014

I'm having some issues getting a tool I'm working on to function correctly. I have some cells, the number of which can vary based upon user requirement, that contain cell addresses. What I need from VBA is to translate these addresses into usable row, column values for the user defined range. Example:

Cell D4 contains F14, which was input by the user.

What is the best way to pass this data through VBA. Right now I am currently using:

Code:

Dim strRange as String 'left out loop parameters
dim TempRange as Range
strRange(i) = Cells(4, 4) 'using TypeName on StrRange at this point yields a string result as expected
set TempRange = Range(strRange(i))

From my understanding that should work but I am getting type mismatch.

I do not have any reason for actually using a string, but would like the user input to only be 1 cell. If there are cleaner ways around this, I am open to them.

Then from the range, I would have two other variables as type long for storing the row and column data using:

Code:

Pathing_Rows = TempRange.Row
Pathing_Cols = TempRange.Column

## VBA - Defining The Range Of Cells Containing Formulas

May 24, 2007

Using the SpecialCells method with a Type = "xlCellTypeFormulas", I can identify the range contains cells with formulas.

But when there isn't any of these cells in the range you've applied the SpecialCells method to, you get a VBA Error 1004. (Doing this outside VBA gives you a "No cells found" box.) How can I skip over this error?

I'm looping through each sheet in the workbook, and defining the UsedRange. I'm doing another loop that uses the HasFormula property on each cell and then performing an action if HasFormula = True, but this results in a lot of extra work processing cells that don't have formulas.

I'd like to further refine the process by defining the range of cells on each sheet that have formulas. But if a sheet contains no cells with formulas, I'd like to skip past it and move on.

I think this is just a simple syntax / code construction question.

## Defining Named Range, Error Criteria

Apr 13, 2007

I am trying to define a dynamic range based upon error criteria. After the first error, all the subsequent cells are filled with the error and I would like to limit the named range to the rows with no errors (one column wide). I am thinking something similar to (realizing this is probably very wrong):

=OFFSET( 'Basin Routing'!\$X\$5,0,0, COUNTIF('Basin Routing'!\$X:\$X,AND(NOT(ISBLANK()),NOT(ISERROR()))),1)

## Defining Variable Range In Sumif Function With VBA Within Table

Jul 28, 2014

I sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.

The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.

VB:
Private Sub ArttotKnop_Click()

Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant

iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....

Since my table grows larger every day I want to use variables.

Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable

Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))

They don't work.

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

## 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).

## Use Variables In A Range?

Aug 23, 2009

I used a macro to get the following code, but would like to do this with VBA code where I use variables and numbers instead of the
macro's ("I568:J568") notation. Thus I would have something like (lRow, 9) : (lRow, 10) or whatever the correct notation is. Basically I'm trying to copy and paste formulas from one row to the next.

## Using Variables To Set Sum Range

Dec 30, 2013

I am trying to sum the range of column B (Being the found match in column A offset 1) to the relevant column set by the variable intcolumn (integer value of the column matching the criteria (in this case column 25)

Code:

The sum does not fail but returns an incorrect figure.

What I am hoping to see is a sum of the following

result sheet offset (0,13) = sum of (column B to the column Y)

## VBA To Get Average Of Range Of Cells Based On Named Range In Different Column

Apr 10, 2013

I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.

I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.

## Sum Of A Range Based On Two Variables

Jun 18, 2009

I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates.

Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.

## Using Variables In Range References

Jun 5, 2009

I am doing some work in which I need a very flexible tool. I will be doing regression analysis on thousands of funds at a time. I need to define the range for each fund by a count function that counts the number of monthly returns that are posted. The code I have so far is below (I underlined where the references use variables):

## Range Using Cells Variables

Aug 3, 2012

I'm trying to use a range(Cells1),(Cells2) all working fine, but now I'm trying to use variables for the Row selection. selection.row as one of the variables, but keep on getting a error.

Code:
Sub Clear_Time()
Dim Rng As Range
Dim pointer As Integer
pointer = Worksheets("Summary").Range("Z1").Value
Worksheets("SubPanel").Range((Cells(10, pointer)), (Cells(52, pointer))).Find(What:="").Activate

[code].....

## Selecting A Range Using Variables

Jun 23, 2008

How would I select a range using variables? For example, say I wanted to say

myRange = Sheets(1).Range("A1:A100"),

but the column was variable. How would I do this if the column number was stored in a variable?

## Determine If Two Range Variables Are Same

Jun 20, 2007

How to decide the two range object variant represent the same range? Plz check the following code, How to decide Rng4 and Rng5 is or not the same range?

Sub IsTheSameRange()
With ActiveSheet
LastRow = .Cells(65536, "B").End(xlUp).Row
Set Rng1 = .Range(.Cells(3, "K"), .Cells(LastRow, "K"))
Set Rng2 = .Range(.Cells(3, "AE"), .Cells(LastRow, "AE"))
Set Rng3 = .Range(.Cells(3, "BQ"), .Cells(LastRow, "BQ"))
Set Rng = Application.Union(Rng1, Rng2, Rng3)
Set Rng4 = Rng.SpecialCells(xlCellTypeFormulas, 23)
Set WhlRng = .Range("A3:DR" & LastRow)
Set Rng5 = WhlRng.SpecialCells(xlCellTypeFormulas, 23)
End With
End Sub

## Variables To Make Up Range

Jul 25, 2007

I'm trying to edit the predefined range in my macro that changes formulas to values.
My current macro looks like this:

Dim vCol As Variant
vCol = Application.InputBox("Select Column", Type:=2)
If vCol = False Or vCol = "" Then Exit Sub
Set UserRange = Range(vCol & "9:" & vCol & "35")
UserRange.Value = UserRange.Value
End Sub

I tried extending the range to include 48:53, but it doesn't work. I tried with this: Set UserRange = Range(vCol & "9:" & vCol & "35" & vCol & "48:" & vCol & "53")

## Use Variables To Delete Range

May 23, 2008

I am trying to select rows using constants I have set-up so that I can then delete those rows. However I don't know how to set-up the row formula to accept the constants. So far I have something like

Dim a As Integer
Dim b As Integer
row(a:b).select
selection.delete

## Autofill Range Using VBA Based On 2 Variables

Jan 14, 2014

I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:

Code:
Sub SummariseSheets()
'collates individual client development data from consultant
'worksheets and compiles in one sheet on summary page

[Code]....

The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.

## Find First Value Greater Than X In Given Range Using Variables

Mar 3, 2014

My data has a bunch of near 0 figures followed by values I actually need followed by more irrelevant 0's than once again followed by values I need and again irrelevant 0's. Looks like

0.1
0.12
3.2
3.4
0.1
0.3
4.5
4.2
0.3
0.11

but with many more rows and numbers. I'm graphing the start and end values before/after zero's.

The first block of numbers I figured out using

=MATCH(TRUE,INDEX(\$C8:\$C150>1,0),0)+7 I then index from that given row.

The span of relevant values I'm looking for constantly changes, so once my numbers deviate too much I stop, using another index formula.

Now for finding my beginning and end points for the next block of data I've combined using VBA with excel formulas. I can't quite figure this part out. I've created Variables for ranges and then used them to find max and min Values.

Public Sub RunCurrent()
'
' RunCurrent Macro
Dim upEnd As Integer
Dim dnStart As Integer
Dim dnEnd As Integer

[Code] ........

## Define Range Variables And Use In Formula VBA?

May 12, 2014

I can't quite get this formula to give me the correct value.

imgur: the simple image sharer

I tried defining as a Range and I can't get it to work right either way. When I hover over my r1 and r2 values the range is correct but it does have "\$A\$227:\$A\$447" quotes around the range so I think that is messing me up.

## Range Variables In Customized Function

Aug 30, 2006

I wrote my function as it is shown below:

Function MyFunction1(r, n, xrange, yrange)
If r <= n Then
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i)
Next
Else
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i) * yrange(i)
Next
End If
End Function

This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})

However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............

## Loop Range Using Variables For Rows

Dec 16, 2006

I need to write a FOR... Next loop that will loop through a range of rows, select the same cell range on each row, perform a task, then advance to the next row. I can't seem to figure out how to substitute the loop counter for the row index. For example:

For counter = 1 To 100

Range("B(counter)","F(counter)").Select
ActiveCell.FormulaR1C1 = "1"

Next counter

## FillDown Range Defined By Variables

Sep 9, 2007

I am trying something out the ordinary, instead of hardcoding the Range, I want o find the last used row of the Range and append, then filldown.
I think my idea is good, but either syntax is off or not going to be allowed to do this.

.Range("F" & lLrwT + 1: "F").FillDown

>>I have posted this on VBAx too.<<