Inserting Into Range Names

Jun 28, 2012

Suppose that i have a table that displays different metrics for each month of the year. I'm dealing with waste management at a thrift store so I'm looking at the weight of donations salvaged, weight of donations sent to landfill and weight sold and so forth. It would look something like this..

weight sent to landfill
weight sold




In my dashboard I'd like to be able to sum the total weight salvaged for certain months like Jan-Feb or Jan-March.

I've named the ranges such as "weightsalvaged.Jan_Feb". So for the sum of the weight salvaged, I have the formula =sum(weightsalvaged.Jan_Feb)

I would like to have another cell where I write "Mar" and the name of the range in the formula automatically changes from

=sum(weightsalvaged.Jan_Feb) TO =sum(weightsalvaged.Jan_Mar)

View 3 Replies


VBA For Inserting Input String Alphabetically Into Column Of Names?

Oct 2, 2013

I have a list of names in the A column of the sheet. I wish to use the input string, a new name, of a vba box to insert into the column of existing names in alphabetical order by inserting an entire row, which I suspect will be the case anyway. Also 'Return' key doesn't seem to be inserting a CR on the page that I typed this request upon.

View 5 Replies View Related

Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

View 9 Replies View Related

Inserting Range Above Selected Row

Mar 8, 2012

I have a range (of 2 rows) that are set up as a header row and sub row. I want a macro to insert these above the active row (ie. where the user places the cursor), but when I select and copy the range in the macro, I don't know how to refer back to the 'active row' because that's not active anymore.

I'd also like the cursor then to be placed into one of the cells in the new row, ready for the user to start editing.

View 6 Replies View Related

Inserting 3 Cells To The Right For Each Cell In A Range

Aug 13, 2014

I have a long row of text cells in a range. Starting with the 1st cell I want to insert 3 empty to the right, then repeat for the next cell that had originally butted up against the first cell until 3 cells have been inserted for each cell in the range.

View 2 Replies View Related

Maintain Range When Inserting Columns

Sep 30, 2009

I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.

I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.

View 9 Replies View Related

Inserting Simple Formula In Range

Jan 1, 2010

I would like to insert a formula in a column, the formula is in column D and must be something like "=C1-B1" for row 1, "=C2-B2" for row 2,...etc. I would like to do it without using a loop cycle in VBA...I have attached an example of the data I'm using..

View 3 Replies View Related

Looping Through Range And Inserting Formula Where Condition Met

Feb 6, 2014

I am having trouble looping through a range and inserting a formula where a condition is met.

My range is "h9:i"

My current code inserts a formula in column i when there is a value in the adjacent row in column h:

[Code] .....

However, because there are so many rows (12,000+) it is taking a considerable length of time to run.

Some of the cells in column i will already contain the relevant formula, so to speed this up I want to insert the formula in column i only when the cell value (in column i) is not null, as opposed to inserting it for all cells within the range.

I know how to loop through the range, but the problem is the 'H9' cell reference in the formula in the code above will need to change depending on what row it is being inserted into- so if it is going into "i31" this will need to be "h31".

View 7 Replies View Related

Stop Inserting Entire Row, Only Insert Range

Mar 24, 2009

Right now, the code below is inserting an entire row when it pastes the values in I only want the values to be inserted in Range (A:AJ).

View 5 Replies View Related

Prevent The Users From Inserting Columns Within A Certain Range?

Jan 7, 2010

I am instantiating Excel 2003 from and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?

View 2 Replies View Related

Replace Range Names In Formulas With Range References

Jul 7, 2007

I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?

View 9 Replies View Related

Inserting Minus Sign In Front Of Numbers In A Range

Mar 19, 2009

i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet

i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.

Numbers with Dr as suffix denote positive numbers
and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.

View 8 Replies View Related

Inserting- Sheet With A Range Of Paragraphs Of Text From Cell

Jan 19, 2009

I have a sheet with a range of paragraphs of text from cell D2:R2.

How can I get it so that if I put an X in the column under the relevant paragraph, it will insert the paragraph of text into the spreadsheet.

For example. Text lies in cell D2. If I put an X in cell D7, I want the text in D2 to be inserted into C7.

View 9 Replies View Related

While Inserting Column Getting Error - Insert Method Of Range Class Failed

Jun 11, 2014

The below code was working absolutely fine yesterday but since this morning I am getting an error: "Insert method of range class failed" at the following line:


Here is the complete code.


I have googled it and didn't find any workable solution. My DataEntry sheet is unprotected.

View 8 Replies View Related

Select Range Using 2 Range Names

May 29, 2009

I know I'm not too bright, but I've really lost it here. Can someone give me the sintax to select all cells between two names: Start thru End

View 5 Replies View Related

Using Range Names

May 16, 2007

I have in column B a link to raw data on another sheet within the same workbook that returns the same of a segment (ie "MSEC"). In column A I have SUMIF statements that total up the volume associated with each segment (ie "MSEC") that is shown in the report. Each SUMIF total is named (through Insert/Name) something that includes the segment name (ie "MSEC_TA"). In column V I want to offer a share number which shows the percentage of the total (ie "MSEC_TA) that each item is. So, I want a formula that allows me to say "E6/the text in B6 & "_TA". Since the text in B6 is "MSEC", that would give me the formula of E6/MSEC_TA, which is the named range which holds the total.

View 9 Replies View Related

Matching First Name In Range Of Names

May 1, 2009

I'm trying to find a way to check if the first and last name that are the content of cell A are anywhere in cell B and beyond. I've included an example: In the example A1 must match B1, C1 and C2.

View 5 Replies View Related

Delete Range Names

Nov 8, 2008

I inhereted a spreadsheet where the designer used range names for all formulas. i hate range names. is there any way i can delete all range names and have all formulas go back to showing cell references instead of range names? When i just go through and delete the range names, i get formula errors.

View 9 Replies View Related

VLookup And Range Names In VBA

May 23, 2009

I have the following
title = Application.WorksheetFunction.VLookup(Q, Range(QandA), 2, False)
QandA is a range that is defined in the active workbook. The code excutes without error but TITLE does not get a value because QandA is reported as empty (when I position my cursor over it).

I have also tried:

title = Application.WorksheetFunction.VLookup(Q, Range("QandA"), 2, False)

View 9 Replies View Related

Range Names Between Workbooks

Jun 21, 2007

In one workbook, i have a sheet with date like this:



In each case, the Data is a block of cells of varying number of columns and rows. Each of the data blocks is a named range. When I reference the ranges from a separate workbook, the results from calling the range don't work at all (return #VALUE!) UNLESS the layout of the second workbook is the same as the source data! so for example, if range Data is in cells A2...F5, then i can reference that data ONLY if i put my formula in cell A2...F5 on the second workbook. this appears downright ridiculous to me and i'm hoping someone can tell me what i'm doing wrong!

View 5 Replies View Related

Hide Range Names

Nov 21, 2007

Suppose I have ten Names in Excel, and I dont like any one see them, How can I hide them (Names not cells)?

View 2 Replies View Related

Using Sheet Names As A Named Range

Feb 6, 2009

I have a namd sheet for everyday of the year in a workbook, so jan1 jan2 jan3 etc. I have a stats page which brings together certain pertinent data from those pages, so a formula on the stats page might look like this =sum('jan1'!$m$25:$m$900). What I would like to do is to name each day - jan1 jan2 etc as a named range perhaps date1 date2 etc so that the above formula would then look like =sum(date1!$m$25:$m$900).

View 2 Replies View Related

Populate Combobox With Range Names

Aug 15, 2007

All I want to do is
put a combo box on a spreadsheet,
have it populated, and sorted - ideally, alphabetically with range names already created.


I'd like to be able to click on the Range Name and GoTo the location.

View 9 Replies View Related

Using Relative References In Range Names

Jun 5, 2009

I have a workbook with a single worksheet. The worksheet includes some named ranges, which are used in a dynamic chart (so that when I add data, the chart updates).

I want to duplicate worksheet#1 so that I can have worksheets 2, 3, 4 etc.

Thus I can paste new data in the new worksheets, and have all my calculations done.

Naturally, the only probelm is the graph. It is using the named ranges from worksheet #1.

Is there a solution, so that I don't have to re-create my graphs on each worksheet? (each worksheet has varying numbers of rows, AND, I will be updating each worksheet with new data from time to time, thus the need for a dynamic chart)

View 4 Replies View Related

Sumproduct Count Range Of Names

Nov 17, 2011

What is the formula for sumproduct to count a say column c for a range of names and as long it match column k for yes.

View 1 Replies View Related

Copying Range Names From One Workbook To Another

Nov 1, 2012

I have a workbook named Br2012. I would like a macro to copy the range names to BR12013.

View 2 Replies View Related

Alphabetize Defined Range Names?

Oct 23, 2013

Why can't I alphabetize all of my defined name ranges in the Name box?

View 8 Replies View Related

Rename Range Names Via Macro

Sep 7, 2007

I am trying to automate the updating of range names in a spreadsheet.

On Sheet 1 Col 1 I have the names of all the existing range names.

In col 2 the new range names.

On sheet 2 the data referenced via range names.

I am getting stuck renaming the old range to the new name.

Sub Test()
Dim sName As String, nName As String

Do While ActiveCell ""
sName = ActiveCell.Value
'MsgBox sName
Application.Goto Reference:=sName
ActiveCell.Offset(0, 1).Activate
nName = ActiveCell.Value
ActiveCell.Name = "nName"
ActiveCell.Offset(1, -1).Activate
End Sub

View 9 Replies View Related

Automatically Updating Range Names

Dec 28, 2009

I have a range of 9 cells located in A2 to A10. The name of this range should be whatever the text in cell A1 is.

The Problem is that the content of cell A1 can change since it is reflecting the content of another cell on a different sheet.

My probelm is that once i define the range to be named according A1 it will keep that name, even if the content of A1 changes.

How can i program in VBA that the name of the cells in range A2 to A10 always is whatever isthe text in A1 is? If Aq changes the name of the cells in range A2 to A10 should change as well; Plus the old name should be deleted.

I think it might work with some event trigger and then a automatic naming of a range. I tried a few things but nothing really worked. (not very exeprianced vit VBA)

View 9 Replies View Related

Poor Performance With Range Names

Aug 24, 2006

why it takes about 100 times longer to set a value using a named range. The code below demonstrates the huge difference between Range("A1") and Range("my_range")

Public Sub testloop()
Dim counter As Long
Dim start As Double
start = Timer()

Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False

For counter = 1 To 34000
Range("my_range") = 9999 ' 101 seconds
'Range("A1") = 9999 ' 1.2 seconds
Next counter

View 7 Replies View Related

Copyrights 2005-15, All rights reserved