Variable Range In Function/Formula
Oct 3, 2007
I have an excel based report where I drop data into a blank spreadsheet each month and my formula (in a second worksheet) finds the relevant information. I do this by using the Match function on the first row of my source data. This finds the column numbers to the data I am interested in. (This is handy as additional columns of data appear almost every month). Most of my formula (in the second worksheet) works on vlookups using the column number from the match function.
However, I need to do a sumif. Sumif requires cell references rather than a column number. How can I get around this? Due to the nature of the report, VBA isn't appropriate in this instance. I'm only producing the initial report; someone else will be updating it each month.
View 2 Replies
ADVERTISEMENT
Mar 12, 2008
How do I pass MATCH or COUNTIF the current dynamic row instead of hardcoding $3? The column is fine hardcoded. Column F has data validation allow List with source equal to dynamic range "userlocation". Column H has data validation allow List with source =OFFSET(userlocation,MATCH($F$3,userlocation,0)-1,1,COUNTIF(userlocation,$F$3),1)
In Column H, this will in effect provide me with a list of values based upon the value of cell F3. Problem is I can't seem to figure out how to reference ROW as a dynamic reference in my MATCH and COUNTIF function.
EX
Column F Column H
Screen1 (if Screen1 = value in my dynamic range pass me back value in a list)
Screen2 (if Screen2 etc.
Screen3
View 3 Replies
View Related
May 29, 2014
MAX function with a variable range. I want find the highest value in a changeable range in a column. The problem for me is, how can I automatically change the range where the highest value is returned.
Here is an example:
Cell A1: 14
In cell A1 is the number entered that specifies the range. In this example the range is 14 rows =MAX(A18:A31).
A2 126.36
A3 126.16
A4 124.93
A5 126.09
A6 126.82
A7 126.48
[Code] .....
Using =MAX(A18:A31) returns 128.57. So far so fine.
But what is the MAX function if the range value refers to the number entered in cell A1? If I change the value in A1 from 14 to 20 how can I make the MAX function flexible that it refers to cell A1 as the range value?
Using the value 20 in cell A1 the MAX function would be =MAX(A12:A31). I can change this manually of course but I want a MAX function that refers to cell A1 as the range value.
I want also mention that the data series is update every day, so that each day a new value is added in column A, e.g. A32, A33, A34. and so on.
View 13 Replies
View Related
Feb 17, 2014
I would very much like to sum a range of values using variables instead of hardcoded ranges. I have tried the following (and variations of )
Range("D2").Formula = "=Sum(Cells(rowIndex1, colIndex1), Cells(rowIndex2, colIndex2))"
where rowIndex1 is starting row number, colIndex1 is the starting column number.....
It does not work.
View 6 Replies
View Related
Jan 27, 2010
I need to calculate the max over a range of figures in a macro.
The "from" is a variable, call it X
The "to" is my counter in a loop so (Cells(-(Counter - 1), 5)
What I'm trying to do is to tell Excel that I want the max from X to the current counter cell.
e.g. Application.Max(Application.Index(X, 1):Cells(-(Counter - 1), 5))
but this gives me an error. I also attach a spreadsheet.
View 5 Replies
View Related
Aug 19, 2009
How should this read? So SheetName is the variable in the range funtion?
Dim SheetName As String
SheetName = ActiveSheet.Name
Set AllCells = Range('" & SheetName & "'!B7:'" & SheetName & "'!B60000")
View 9 Replies
View Related
Apr 22, 2006
I am trying to produce VBA code that will perform the CountA function on a variable number of lines in a column. My macro first sorts the worksheet by department number and then inserts 2 blank rows when there is a change in departments. Most departments have several rows of data, but some may have only 1. In column N of the first blank row following each department, I want to count the number of rows for that department.
View 3 Replies
View Related
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.
View 2 Replies
View Related
May 17, 2008
I will show with an example of what I would like to do. I italized and underscored the part of the formula.
---A----B
1 24 formulaB
formulaB = "= CELL("contents",DifferentSheet!G(A1))"
I want B1 to contain the value from the cell G24 in sheet "DifferentSheet". I want to use the value in A1 to determine what row on "DifferentSheet" I should use.
View 2 Replies
View Related
May 28, 2008
I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I can’t figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.
Template = Application.InputBox("What is the name of the file ending with (.xls)")
If Template = False Then
cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly)
If cont1 = vbOK Then
Exit Sub
End If
End If
View 3 Replies
View Related
May 29, 2008
I am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column
e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17
As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.
I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17
View 3 Replies
View Related
Jan 17, 2012
I am trying place the "sum" formula into multiple cells, changing with "i" (itteration process).
Something like:
.Range(17,counter).formula = "=Sum(B2:B20)"
when the couter itterates, the same line of code should give:
.Range(17,counter).formula = "=Sum(F2:F20)"
My actual code looks like: ... which doesnt work.
.Offset(17, " & ColumnHeadCout & " + 1).FormulaR18C" & ColumnHeadCout + 1 & " = ""=SUM(R[20]C[" & ColumnHeadCout + 1 & "]:R[" & NumberStreamElem + 20 & "]C[" & ColumnHeadCout + 1 & "])"
View 9 Replies
View Related
Jul 6, 2007
I have below code (option 1) I was hoping would do what I need except instead of copying formula from A1 it copies values... (option 2) copies formula but does not preserve references...any ideas how to copy exact formula so that references are not changed?
Assumptions:
A1 formula = SUM(B2:E2)
in A5 I would like to copy exact formula to keep references to row 2...
I agree I could use $ in original formula but that would complicate other requirements.
OPTION1:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
Range("A5") = vRange1
End Sub
OPTION2:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
vRange1.Copy
Range("A5").PasteSpecial
End Sub
View 9 Replies
View Related
Aug 9, 2007
I've developed a code to load csv files in excel and do some formatting on it. In addition I want to add a few cells which calculate making use of the data in the file. The data tabel (resulting after the csv import) always has the same number of columns, but the number of rows varies depending on the imported file.
My problem now is that I need to change the formula for my calculation depending on this number of rows.
This is what I got so far (not including the csv import):
Option Explicit
Dim varRow As Double
Function fctCountNrRows() As Double
Range("A1").End(xlDown).Select
varRow = Selection.Row
End Function
Sub subAddTotals()
fctCountNrRows
Cells(varRow + 2, 1).Select ..............
View 9 Replies
View Related
Nov 27, 2007
It is useful to use lookup function to find the student name. However, if the table array size is variable
(say, a new student enrolled and the table array will be $A$2:$B$7), the table array size will be changed
and we need to change the vlookup function. How to avoid it? Can we use some kind of variable for the
See attached file( book1)
View 9 Replies
View Related
Aug 3, 2006
The problem lies in the following line
Range(Cells(1, a), Cells(b, a)).Formula = _
"=(rc[-1]-" & min & ")/(" & max & "-" & min & ") "
When it is executed i get the 1004 error ("application-defined or object-defined error)...
min and max are defined (as worksheetfunction.min and max of a selection), and their values are correct. The problem seems to lie in the max variable, namely if i simplify the formula to just
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & min & ")"
it works ok, but if it is
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & max & ")"
I get the error.
I am completely baffled considering both variables are defined in the same way i.e.
min = Application.WorksheetFunction.min(Selection)
max = Application.WorksheetFunction.max(Selection)
View 9 Replies
View Related
Oct 8, 2007
Here is what I would like to do: Have a formula like Max(B12:B14) change to Max(B12:B18) when I change the value in different cell from 3 to 7. I would like to stay away from the macro world and keep in formula world if that is possible.
View 6 Replies
View Related
Dec 3, 2013
Code:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)
I am using the above formula in my code with two Named Ranges
Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)
=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]
I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.
I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49
View 2 Replies
View Related
May 18, 2009
I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)
The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.
For example, I want I37 on most sheets to have the same formula. So I have the following:
View 3 Replies
View Related
Apr 2, 2009
I have several named ranges with 4 column each. The named ranges have all the same names but with the tow last digits ranging from 00 to 50 by increments of 5 (ie 00, 05, 10, 15 etc.). The 4 columns for each of the named ranges have the following headings; 10, 100, 50 ,70. I have two cells with drop down lists with the list of the suffixes of the named ranges in cell e5 (ie 00, 05, 10, etc) and in g5 a drop down list with the 4 headings for the columns (10, 100, 50, 70)
I want to refer to these 2 values so that in column B, I can get the values of the column chosen for the given named range. For example, the column "100" for the named range THRESHOLD_10. In cell C1, I entered the following formula ="THRESHOLD_"& E5 which gives me the named range name. I entered the following function in the first cell of column B = index(C1,2,G5) but get a #REF! answer. If I write the following formula =index(THRESHOLD_10,2,G5) I get the right value.
how to get about it, and indirect(C1), but still gets a #REF! message. When I evaluate the formula, the named range appears with quotes "" which might be the reason that it cannot work. I am nearly there...but for the syntax and cannot get that working!
View 3 Replies
View Related
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
Mar 3, 2014
I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.
[Code] ............
View 1 Replies
View Related
Apr 4, 2008
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies
View Related
Mar 11, 2008
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
View 3 Replies
View Related
May 19, 2014
Is it possible to use a variable in Countif?
See attached sheet. When a number repeats for the 3rd time, I need to reset the
Countif function.
For example;
Countif($A$5:A20,A20).
I need to change the $A$5 to the current row number so we can start the count again. I need to do this several times during the sheet.
We are counting numbers until they repeat for a 3rd time and then we start the count again at that point.
View 1 Replies
View Related
Dec 9, 2008
I'd like to compare values from different sheets. My sheets contain data from different years. I have sheets named 2000, 2001, 2002, 2003.... I also have a sheet named "compare". Now I'm using functions like: =('2008'!J13/'2007'!J13)-1
which gives me the relative change between years 2007 - 2008 in the cell J13.
I'd like to use the sheet name as variable. For example: "year 1" is given in cell A1 and "year 2" given in cell A2. Then I could enter (for example) 2007 in cell A1 and 2008 in cell A2. How can I use these cell values in the function? I’ve tried something like: =('A2'!J13/'A1'!J13)-1. I assumed that 'A2'!J13 would be the same as '2008'!J13. It didn’t take me long to figure out that doesn’t work. What would be the right way to do this?
View 3 Replies
View Related
Aug 12, 2008
I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc
What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?
View 9 Replies
View Related
Jan 28, 2009
I've searched for an example of function that returns a variable, however, I'm unable to find one other than the one just has return variable on the end. I was wondering what I have wrong in the following code. This is placed in a module to make it available to all my worksheets.
Public Function findLR(Worksheet, Column) As Integer
LR = Worksheet.Cells(Rows.count, Column).End(xlUp).Row
return LR
End Function
View 9 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Feb 19, 2010
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
View 6 Replies
View Related
