# How To Reference Cells Value To Define Range Reference

Jul 14, 2014

Is it possible to reference a cells value to define a range reference?

[Code] ......

I am trying to define the row value in the range reference with a value in a secondary cell?

## Udf Vb Looking To Define A Dynamic Absolute Reference

Mar 13, 2007

I'm trying to create a user defined function to drop into dblookup functions that will always reference the first cell in an associated column and the first cell in the row of an associated column.

I came up with the following, however the problem is that the udf always returns the values for the active cell's, row and columns.

Function SRCref2()
Application.Volatile

myfield1 = ActiveCell.Column
Dim1 = Columns(myfield1).Range("a1").Value
SRCref2 = Dim1
End Function

Function SRCref3()
Application.Volatile

myfield2 = ActiveCell.Row
Dim2 = Rows(myfield2).Range("a1").Value
SRCref3 = Dim2

End Function

I would nest these in a formula such as "=srcsum(srcref2(), srcref3())".
Ideally I could then use this same funtion anywhere instead of having to use and worry about the accuracy of "=srcsum(\$a\$2,\$b\$1)"."=srcsum(\$a\$3,\$b\$1)"."=srcsum(\$a\$4,\$b\$5)".etc.

It seems like there should be a current cell method or something but I can't find it and I'm not smart enough it seems to use vb to designate some kind of floating intersect as a base for a row and column reference.

## Cell Reference- Able To Reference Two Cells To The Left Even As More Cells Are Added

Jun 11, 2008

I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C

As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.

I also need E (which has also shifted over one) to sum A,B,C, and C2.

Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.

## Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

## Dynamically Reference Range Of Cells?

Jun 27, 2012

I have a 'receipt' worksheet with about 500+ lines of data, here is an example of what could be in cell A166: 1.1 NET_AMOUNT 742,523,253.83

In another worksheet, is there a way to look at the whole worksheet for column A in receipt to say:
if 1.1 is true, capture text to the right of 'NET_AMOUNT' then format using =Dollar?

## Reference Range Of Cells For A Log From Closed Workbooks

Nov 20, 2012

I've been trying for a while not to reference a range of cells in closed workbooks to create an ongoing automatic log.

I've attached a test log with the details required and the cells are staggered, I have been using the formula;

='NETWORKPATHFOLDER[121113 12.42.xls]Enquiry Form'!\$G\$1

then the cells are (SAME PATH) J1, A5, A7, A9, A11, A13, A15, A17, A19, A21, A31, B31, E31, J31

and the attached test will placed then in that order under their respective headers.

So, all in all, is there a VBA code where it can run it from closed workbooks in the order above?

log text.xls

## Reference Range Cells From VLOOKUP Results

Oct 22, 2009

I have a workbook where one tab contains the data that I am given:

Project-A data1 data2 date1 date2 date3
Project-B data1 data2 date1 date2 date3
Project-C data1 data2 date1 date2 date3
Project-D data1 data2 date1 date2 date3

In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.

So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.

## How To Add Reference To Array Or Range Of Cells In SQL Query

Jul 15, 2014

Using VBA, I am importing data from Access (Access2007) into Excel. The code that I use works as required but I need to refine it so that I can filter for the records that I need to import. I can import ALL records. I can import selected records by manually changing my SQL query. I would like to reference an array of values or a range of cells so that I do not need to manually change my query every time I want to run it.

[Code].....

This code gets all records for "Jeff" and "Bob" from the Access database. I need to query about 100 names at a time so manually inputting them is going to take forever. How I can make it work?

By using an array:
[Code].......
or
by using a range of cells:

## Fastest Way To Reference A Cell - Range() Or Cells() ?

Dec 22, 2008

I have a loop that executes roughly 7.7 million times when my VBA program runs. Neednless to say it take a long time to run - usually a couple days.

The inner-most loop contains a line of code from way back in my early vba programming days when I knew even less than the small amount I know about programming now (and if you can follow that sentence you might be able to understand some of the spaghetti code I write :-) ).

for k = 1 to n

if Worksheets("Personell").Range("D" & Trim(Str(k))) > dtCompleted then ...

Would it be faster to use this syntax (which I just found out about):

if Worksheets("Personell").Cells(k,4) > dtCompleted then ...

It would cut out 2 functions calls, trim() and str(), so it would be faster, right?

I am almost embarassed to post the Range("D" & Trim(Str(k)) line of code because it looks so convoluted now, but that's how I learned to program, just fumbling through until it ran...

So in short, I just wanted to confirm that the cells() syntax run faster before I spend an hour editing and tested.

## Cells Range Reference Not Working Properly

May 25, 2009

I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.

Dim cnt1 as integer, cnt2 as integer,cnt3 as integer

cnt1 = 2
cnt2 = 50
cnt3 = 2

dim myrange as range

set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))

I have no idea why it won't work. I'm basically searching a columner range of cells.

## Use Range Of Selected Cells As Relative Reference

Mar 8, 2013

I have a data that is split into multiple cells and needs to concatenated. Unfortunately, the number of columns wherein lies the data varies throughout the workbook. As such, I wanted to select a range of cells, define this range as the reference point for the macro, run the macro, then move on (selecting a different number of columns on the next try).

For example, I have this:

A
B
C
D
E

1

No
Not Very Far

[Code] .....

I want to select cells A1:B1, run a macro concatenated the two columns, then select cells C1:E and run the same macro to get this:

A
B

1
No
Not Very Far

[Code] ........

So far, I have this:

Code:
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(5, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC,RC)" '

## Reference Cells Relative To Activecell In A Range

Nov 4, 2008

I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudo
If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......

*****end pseudo

I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....

Here is my code so far:

Worksheets("Generic SPC").Activate
Worksheets("Generic SPC").Range("c2").Select

'Do for all cells in the row
Do

ActiveCell.Offset(0, 1).Select
If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0
ElseIf Application.WorksheetFunction.Sum(Range _
("ActiveCell.Offset(-2, 1), ActiveCell.Offset(-2, 3)")) = 0 Then
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value
Else: ActiveCell.Value = 0
End If

Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = True

******End Code

Range doesn't like activecell.offset. Does anyone know how to do this?

## Reference To A Range Of Cells As Input For A Macro

May 13, 2009

I want to create a macro which asks some information like functions do. For example, I would like the macro to ask for some data to be filled by the user. For that purpose I thought about using the inputBox vba function. I wanna fill the field in the inputBox with a reference to a range from an excel sheet. So, I click on a cell and then I try to hold ctrl + down cursor to get all the range starting from that cell on until the last consecutive filled cell on the sheet. Nonetheless, the problem I have is that when I do ctrl+down cursor, I just do not get the reference I want, because it seems that the ctrl+down cursor combination does not work in the inputbox for filling a reference to a range of cells. Does anyone know some workaround to achieve this?

## Convert Cells Reference To Range Address Code

Sep 7, 2006

I have to convert a range reference in the form of

Range(Cells(1,1),Cells(2,2))

to a reference in the form of

"A1:B2"

## Multiply Range Of Cells By Cell Reference Without Changing Hard Coded Values?

Aug 20, 2014

I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.

## Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter

=--(AND(\$AX8>\$AA\$4,\$Y8>0))

and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell \$AA\$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

## Excel 2007 :: Convert Absolute Reference Into Relative Reference

Feb 4, 2014

I have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.

Code below.

Sub FAIL()
'
' FAIL Macro
'
'
Sheets("Reformatted").Select
Columns("C:C").Select
Selection.Find(What:="stop", After:=ActiveCell, LookIn:=xlValues, LookAt _

[Code] .......

## Stop Formula Column Reference Changing On Insert But Not Row Reference

Mar 6, 2008

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

## Absolute Reference (cell Reference Behind The Table)

Mar 11, 2009

I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

## Convert Cell Reference To Worksheet Reference

Jul 20, 2014

I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:

Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say

AAB
ABC
CDE

And have a formula in column B that converts this to

=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)

I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?

## Use Cell Value To Define Cell Reference

Sep 18, 2007

I want to define a cell range using a fixed column letter and then row numbers that are held as values in the spreadsheet somewhere. So for example I want to enter a formula = SUM(A4:A35). The problem is that the ranges I want to define are irregular and the row numbers I need have been calculated elsewhere in the spreadsheet. Say the number 4 is contained in cell B35 and the number 35 is contained in cell C35. So I want to say something like =SUM(A"plus number taken from cell B35":A"plus number taken from cell C35")

## Circular Reference With Formula But No Reference To It

Mar 26, 2009

=INDEX(INDIRECT('Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5&"!\$A:\$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5&"!\$A:\$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5&"!\$A:\$DC"),1,),0))
Where
A1= "M16" and B2= "185%RPIT630"
'Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5=QxTermAge63
Can some on tell me why this is raising a Circular Reference!!

## Define Range By Selected Cells

Jul 19, 2014

I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.

[Code] ........

Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'

'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.

## Range Reference

Dec 21, 2009

I am using a multi-tab spreadsheet in which one tab has an outside-generated report dumped in as the input, with other tabs referencing its data. The Input tab has two sets of near-duplicate data, and I need to reference the second set, which begins in different rows for each Input report. I can't do a specific vlookup, because the reference appears twice in the tab but I need the data following the second reference. Can I name a range, beginning at a changing point (the cell which reads "Exposure:Net") and continues to the end of the tab, and have functions referencing it work properly?

## How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

## Define Range Object Based On Cells Not Null

May 20, 2006

The problem that I am having today is defining a range object that cannot include null cells. If it does include Null cells then the filterwill fail. the cells that I need to define are all in a cohesive unit. the other thing to know is that the cells that are not null will never be mixed in with cells that are null. so for instance you might have a range of cells from one to 100, the first 50 might be full. the last 50 would all be null. in that situation I would need to loop through those cells to define a range object that would just see the first 50 cells ....

## Dynamic Range Reference

Apr 22, 2008

I have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly?

For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.

## Getting A Range Reference From A Cell

Jan 16, 2009

i need to copy a range from one workbook ("weekly data") to another ("Yearly data"). The range to copy is called 'weekly data'. The ranges to paste to are called 'week 1' through to 'week 52' (this is the yearly data sheet). On the yearly sheet there is a cell Po1' that week on week will state the week number ie 'week13', 'week14' etc. code to use this data in 'P1' to tell the macro to copy to the appropriate week. I have attached as far as i got (then failed due to lack of experiance).

presuming that both books are open

HTML windows("weekly data").activate
sheets("weekly transfer").select
range("weekly data").copy
windows("yearly data").activate
'only one sheet in workbook
range ' data from p1 .paste

## Reference Sheet Not Range?

Nov 17, 2011

How Could I change the below line to reference the sheet "NO" not just a range.

Set Source = Range("NO!A1:BK10000").SpecialCells(xlCellTypeVisible)

## Array And Range Reference

Jan 9, 2009

In a worksheet is a table:
a b c d
e f g h
i j h l
m n o p
q r s t

I'm looking for a procedure to (with the help of
an array) reproduce this table exactly under the original one
with one row between them but in the folowing form:

q m i e a
r n j f b
s o h g c
t p l h d

the procedure should be applicable on any haphazard
regardless of its place on a worksheet