Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Using Offset to name a range

I have a worksheet that filters data to
b17 through k(row number will vary)
can someone tell me what is wrong with this function?
I admit i have a rough time with offset!
=OFFSET(GwrStmts.!$B$17,0,0,COUNTA(GwrStmts.!$B:$B ,1),COUNTA(GwrStmts.!$2:$2))

Thank you-Jennifer
--
Though daily learning, I LOVE EXCEL!
Jennifer


Post your answer or comment

comments powered by Disqus
Hello again,

I'm working on the following piece of code:


	VB:
	
Worksheets("Monday1").Select 
For Each x In Monday1 
    Worksheets("Front Page").Select 
    For Each y In Shiftrange 
        If y.Value = x.Value Then 
            y.Offset(0, 1).Select 
            Selection.Copy 
            Worksheets("Monday1").Select 
            x.Offset(0, 2).Select 
            ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone 
        End If 
    Next y 
Next x 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(The "Monday1" in line 2 is a variable, a specific set of cells on the "Monday1" Worksheet. Shiftrange is another variable, a specific set of cells on the "Front Page" Worksheet.)

This is designed to look at the shift name next to a staff (e.g. 0800-1600), then check for that value on the Front Page, select the data next to the 0800-1600 entry and return it to the first page.

My problem is with the line:

y.Offset(0, 1).Select

Rather than just selecting the one cell to the right of "y" (as it does now), I'd like it to select a range, from the cell one column over, to the cell 56 columns over.

Any help will be most gratefully appreciated!

Thanks in advance,
Andy

PS - reading this back I'm not sure how clear it is, please let me know if I can clarify anything?

My code is extremely slow because it writes a row by row. I am trying to use arrays to fill a range for a problem which is essentilly this:

Let x = 1 to 10
Let y = 1 to 10
Let R = f (z)

f (z) is calculated for various pairs of x and y (coordinates) such as:
(x1,y1), (x1,y2), (x1,y3)....(x10,y9), (x10,y10).

I am looking for a code that will fill columns A' and 'B' with x and y in pairs (each x with each y) and values of R in column 'C'.

Any help please?

Is there a way to use sumif to sum a range of values based on two conditions.
In other words I would like to sum a range of values in col c if values in
col b are equal to "2006" and values f are equal to "proposed".
Thanks

--
hwy

I am trying to pull a number from a cell and use it to select a range of cells

Dim days as integer

days = range("A1").value

range(B1:C & days).select

what is the exact syntax including any other line of code required. Assume I know nothing.

I’m writing a macro to assist me in creating a group of dependent lists. I need the syntax for naming a range with the content of another cell. For example: Cell A1 contains a word. I want to name the cell range B1 through B5 with the word currently populating cell A1. Is there a command I can use?

Right now, starting from A1 I use the copy command to copy the content of cell A1. I then select the range B1 through B5, place the cursor in the NAME BOX and press paste. See my crude macro below which performs everything but pasting the name.
    Selection.Copy
    ActiveCell.Offset(0, 2).Select
    Range(Selection, Selection.End(xlDown)).Select
End Sub
I understand that the name pasting may not be something I can script. I have everything I need accept actually naming the range.

In my spread I thought I'd found a way to sumif a moving range. However, as a check I tried one set of data with different criteria and the #'s were off. The formula I'm using:

=SUMIF(H$4:H$55539,OFFSET('MassHub Fwds'!C$4,MATCH($K4,'MassHub Fwds'!C$5:C$70,0),1),OFFSET(G$3,MATCH(K4,A$4:A$55539,0),0):OFFSET(G$3,MATCH(DATE(YEAR(K4),MONTH(K4)+ 1,DAY(K4)),A$4:A$55539,0)-1,0))

The criterion for the SumIf (first offset) are basically prices on another sheet which seems to work fine, I've tested this without the sumif and does what it's told. However, when I try to define the [sum range] with offsets, the #'s are thrown off.

What the 'match' in the sum range offsets are looking at are dates. To paint the picture:

Date; Hour; SumRange; Range; Criteria; Output
9/1/06; 1; 111.50; 61.65; 9/1/06; ...
9/1/06; 2; 90.65; 60.22;
9/1/06; 3; 101; 61.65;
.
.
.
10/1/06; 1; 60.35; 61.65

Basically what's happening is the 'Range' is full of values from another spread (this is what the first offset looks up). The sum range offsets look up the date (the 'Criteria' for my output) for which to sum. One underlying problem I feel is that the 'Range' has multiple values which are the same that occur on different dates; so a SumIf without a 'begin date' and 'end date' will sum up values which are not within the criteria.

Any ideas?

I am attempting to write a vba script to name a range that varies when I refresh a table. The table is linked to a sql query, so the table length can shrink and grow.

Here is my code:


	VB:
	
Sheets("MeditechData").Select 
Range("A5").Select 
Dim Lrow As Long 
Range(Selection, Selection.End(xlDown)).Select 
Lrow = ActiveCell.row 
Range(Selection, Selection.End(xlToRight)).Select 
ActiveWorkbook.Names.Add Name:="DataRange", RefersToR1C1:= _ 
"'=MeditechData!R5C1:R'" & "Lrow" & "'C14'" 
ActiveWorkbook.Names("DataRange").Comment = "" 
Range("DataRange").Select 
Selection.ClearContents 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The debugger does not recognize the statement: Range("DataRange").select

I am using the Names.Add Name in another part of the code later to copy the new data to another spreadsheet. So it probably will not work there either.

What is the proper syntax to name the range without had coding an exact row number in the right half of the RefersToR1C1: portion?

thanks

Glen

I am struggling with range naming where the name is to be the text value of a particular cell.

Briefly: A userform listbox places names in a column in Sheet2, starting with cell E1 and subsequent transfers from the listbox (which is multiselect) going to F1, G1 etc. There will be between 6 and 15 names in each case.

A seperate textbox is used to assign a number to each multiselection, this number is added to "Div" and placed in row 22 of the column to which the multiselection is transfered.

The text in this cell is used for other things later but I also want to use it to Name the range to which it refers. This name is then immediately used to populate another listbox in the userform, and will be used repeatedly later on in the project.

Here is the relavant snip of code that I have been trying to get to work but I get 'object required' for the ActiveWorkbook.Names.Add.......... line. I did manage to get rid of that once (can't recall how!!) but then the ListBox2,RowSource gave an error.


	VB:
	
 DvNm = Sheet2.Range("IV1").End(xlToLeft).Offset(21, 0) 
Set Rngc = Sheet2.Range("IV1").End(xlToLeft) 
Set Rngp = Sheet2.Range("D65536").End(xlUp).Offset(1, 0) 
 
DvNm.Value = "Div" & TextBox1.Value 
 
Range(Rngc, Rngc.Offset(15, 0)).Copy 
Rngp.PasteSpecial (xlPasteValues) 
Application.CutCopyMode = False 
 
ActiveWorkbook.Names.Add Name:=DvMn.Value, RefersToR1C1:="Range(Rngc, Rngc.Offset(15, 0))" 
 
 
Application.ScreenUpdating = True 
 
ListBox2.RowSource = DvMn.Value 
TextBox1.Value = "" 
ListBox1.Enabled = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I think it may be a matter of placing quotation marks in the right place but I cannot get it right.

Hope somebody can help.

Thanks

I have four ranges named A, B, C, D.
I need to be able to copy (and paste) one of them when its name is
entered in a cell.
What is the macro syntax to get the value in a cell and use it to select
a range, please?
I would attach this macro to a button (which I know how to do) to allow
the user to enter the range name and click the button to copy it to a
fixed location (that I know how to specify)
Thanks!.

Hi all,

I've been searching the group/forum and can't find what I'm looking for though I might be approaching this wrong.

I have an array worksheet function in the format {=SUM((Range1 = Crit 1)*(Range2 = Crit2)*(Range3 = Crit3) * (Range4 = Crit4) *RangeToSum)} Range1 to Range4 are hardcoded ranges and I originally had hard coded the RangeToSum column and it works fine. I've now had to add the flexibility to change what column RangeToSum is.

The data is in this format:
Crit1Col ... Crit2Col ... Crit3Col ... Crit4Col ... Mth1 ... Mth2 (etc to Mth12) - Where Mth1 to Mth12 is the RangeToSum columns.

What I need to do is depending on the month selected via a combobox, select the column for that Month. I've created this formula: =OFFSET(Mth1Col,0,MATCH(MonthSelected,RowOfMonths,0)-1) and subtituted it in the Array for the RangeToSum range.

Now i've read that the OFFSET formula is supposed to return a range, so by my reckoning if I substitute the RangeToSum hardcoded range with that formula it should work ... but it doesn't.

If I write the formula as =SUM(OFFSET(Mth1Col,0,MATCH(MonthSelected,RowOfMonths,0)-1) then it sums the entirity of the column for that month .... which shows it's working out the offset correctly.

When I use the Evaluate option under Formula Auditing, it puts the correct range into =SUM(CorrectRange), but when I take the =SUM() out then it comes back with the value corresponding to row that the formula has been entered (e.g. put the =SUM(OFFSET(....)) into A15 on Sheet1 it returns the value on row 15 of the column of the range on Sheet2 where the data is).

What I need to work out is how to get the OFFSET formula to return a range that will work with the original {=SUM((Range1 = Crit 1)*(Range2 = Crit2)*(Range3 = Crit3) * (Range4 = Crit4) *RangeToSum)} array formula OR can you tell me where i'm going wrong and if I need to rewrite the formula from scratch I don't mind?

I hope I've explained myself sufficiently.

Thanks in advance!

Peter M.

Hi all,

I need a macro to name a range at column B and C. I want to use the upmost cell of column B as a name of that range, and cells from B2 to last non-blanc cell of B and C would be the range itself. (B and C columns have always the last non-blanc cell at the same row)

How about if after the area has been named, I insert rows or columns so that this named range moves to new position, is it possible to automatically update the range to still mean the right cells?

Thank you all

-Juhanen-

I have a worksheet that filters data to
b17 through k(row number will vary)
can someone tell me what is wrong with this function?
I admit i have a rough time with offset!
=OFFSET(GwrStmts.!$B$17,0,0,COUNTA(GwrStmts.!$B:$B,1),COUNTA(GwrStmts.!$2:$2))

Thank you-Jennifer
--
Though daily learning, I LOVE EXCEL!
Jennifer

Hello everyone,

This might be really basic, but here goes...

I am trying to import addresses downloaded from EarthLink web mail and saved
in Excel as a CSV file into Outlook 2003. While going through the wizard, I
am told that Outlook can't import the file because the file has no named
ranges. It tells me to use Excel to name the range of data you want to
import.

In Excel 2003, I looked in help to figure out what that meant. I was told
to select the cells and "name the range." Does that mean that I select the
cell range and in the name box, write a word label for the range like
"Addresses" or does it mean that I select the cells and type in the actual
range that I have selected like A1:AE1089?

After that what do I do?

I tired selecting, labeling with A1:AE1089 and clicking save, but it didn't
work. The instructions are a little too cryptic for my brain! Anyone have
some help for me?

Thanks in advance,

Amy

Can somehere tell me how to name a range in excel using VB code. Here'
how I get the range:

Range("L1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

As u can see the range changes, but I would like to create the sam
name for whatever the range is?

Thanks..
Krazylai

--
Message posted from http://www.ExcelForum.com

I'm building my first excel "web app." Part of it is in the attached file.

I need a formula result which is a range of cells. For example:

if Input cell E15 is >= 250 and =< 500 , then formula result is Range J14:J2

If input cell E15 > 2500 , then formula result is Range N14:N22

This result will be the "beginning point" of a complex set of calculations, which require whatever values happen to be in the formula result.

(the attached sheet illustrates the question).

Please note that I don't know much about naming ranges, or how to use them. I just know that it's possible to name a range.

Thanks much!

I'm a bit new to the excel "range" object type. I was suprised to see that
while I can do:

dim chunk as Range
chunk = .Range(A5:B6)

I apparently cannot do:

dim chunk as Range
dim stuff as string
string = "A5:B6"
chunk= .Range(string)

How can I concatenate up a string describing a range, and then use it to
define a range object's target cells?

- Ross.

Hello
I'm tying to use vlookup to pull a cell range out of a table and count a specified collumn. How I tried this is by using The folloing formula which would go in K4 on the attached sheet.

=countif(vlookup(K3,F:H,3,0),"<"&J3)

I realize I could use if functions but I need my table set up so I can just use the fill handle to add more data in to my look up table.

My goal is to be able to change the value in Cell K3 and have the corresponding column counted.

Hello everyone,

This might be really basic, but here goes...

I am trying to import addresses downloaded from EarthLink web mail and saved
in Excel as a CSV file into Outlook 2003. While going through the wizard, I
am told that Outlook can't import the file because the file has no named
ranges. It tells me to use Excel to name the range of data you want to
import.

In Excel 2003, I looked in help to figure out what that meant. I was told
to select the cells and "name the range." Does that mean that I select the
cell range and in the name box, write a word label for the range like
"Addresses" or does it mean that I select the cells and type in the actual
range that I have selected like A1:AE1089?

After that what do I do?

I tired selecting, labeling with A1:AE1089 and clicking save, but it didn't
work. The instructions are a little too cryptic for my brain! Anyone have
some help for me?

Thanks in advance,

Amy

Dear Expert,

I would like to use Excel recorder to name a range in VBA.

The table may have different number of rows every day in a file. It can start at Row 13 one day, maybe starts as Row 17 the other day.

Table column starts with "Age Bucket" in Column A.

Using recorder, it has to define a fixed address which may be true for one day, but wrong for next day.

In this case, I was forced to set the range and name it as "DATA"

"=Sheet1!R13C1:R18C8"

Is it possible to select the data in table after column header "Age Bucket" and name it as "Data" in VBA?

Thanks for help.

Sub Macro1()

Application.Goto Reference:="R1C1"
Cells.Find(What:="Age Bucket", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, MatchByte:=False, SearchFormat:=False).Activate
ActiveCell.Range("A1:A28").Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
"=Sheet1!R13C1:R18C8"
ActiveWorkbook.Names("Data").Comment = ""
End Sub

I have a form with two date picker controls. I have DTPicker1 as StartDate and DTPicker2 as EndDate. How do I code them to select a range of rows to print?

Thanks you for your help!

Is there a better way than this to name a range at the worksheet level?

ActiveCell.Name = “ ' ” & ActiveSheet.Name & “ '!” & "test"

Thanks

Hi,

Does any one know how to name a range in code as test from b22 and xldown.

Thanks For your help

Is there a way to name a range beginning with a number?
It doesn't accept it. Is there a way around this?
range name examples: 2DBSUS
3DBSUS
thanks

How can I name a range that exists in a different workbook?

Thanks,

Keith


No luck finding an answer? You could always try Google.