Updating Named Range When New Contact Is Added Using R1C1 Format With A Variable
Feb 1, 2010
The code below is for a macro that allows the user to create a list of contacts. The column containing the names of these contacts are then referenced by a userform (code not present) by way of a Named Range. I want to update the named range whenever the user adds another contact so this new contact shows up in the user form.
I am currently referencing the range containing the names using R1C1 style, but I cannot get the variable aspect to work correctly.
View 2 Replies
ADVERTISEMENT
Apr 15, 2008
I'm trying to figure out how many Widget As are in Order 0001, for example.
Order # Product
0001 Widget B
0001 Widget A
0001 Widget A
0001 Widget C
0001 Widget A
0002 etc.
I would think that I should use a sum(R1C1:R1C1) format to figure this out, but I might be making this harder than necessary.
View 9 Replies
View Related
Nov 8, 2006
I have several ComboBoxes & ListBoxes on a Userform that populate by referring to Dynamic Named Range in the RowSource property. Also on the Userform is an area to edit/add/delete items in these named ranges. If I Add or Delete an item, the Named Range does not automatically resize--or at least the Menus don't "resize" accordingly--unless I unload the UserForm and load it back up. Is there a way to refresh these Comboboxes without reloading the UserForm?
View 2 Replies
View Related
May 4, 2006
I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?
View 2 Replies
View Related
Jun 28, 2012
1. Can a Named Range contain blank cells and update those blank cells as data is being entered?
Example: If I have a range
='Active List 6-25-12'!$A$1:$E$44,
And data only goes to $E$35 and as I add more data to the rows it will populate in any formula totals being pulled from that range?
2. If it is not automatically updating ranges, is there a refresh button to manually update the total? !: The Red refresh !
- Also redefining the data range values doesn't seem to work. I have to delete and recreate them. Frustrating on a 499 row document.
View 1 Replies
View Related
Nov 13, 2007
I am working with a worksheet that has hundreds of named ranges already in-place. When the creator defined these ranges, they defined them all statically. I would like to update each named range to be a variable named range.
I was thinking about doing a massive find / replace in the VB Editor, but I can't seem to find the named ranges. I assume they were defined from within Excel by the Insert > Name > Define method, and not through VBA and that is why I can't find them. Is there any way to simplify this process instead of manually trying to update each and every range?
View 9 Replies
View Related
Oct 5, 2011
There is a formula
=vlookup(a1,Named_Range,2,false)
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
View 1 Replies
View Related
Jul 24, 2009
I have a worksheet on which users will enter data one row at a time, in columns A-K. When the user starts to enter data in a new row #, that is, when they make any column in the next, unused row have non-blank value, I'd like the sheet to update the borders of the row for columns A-K. I want the Range from A1 to K# to have full borders, so that the whole data set is outlined and easier to read.
For a bonus, I'd like the formulas from the previous H, I, and J cells copied down into H#, I#, and J#, adjusted appropriately for their reference changes.
Normally, I would work around the edges of such a VB problem by recording the actions manually and then modifying that code as I learned more. But I'm not sure if what I've done is the right way to start that process. I think that if I use the CurrentRegion property, and the Worksheet_Change event, I'll be well on my way.
View 2 Replies
View Related
Nov 8, 2011
It's probably O so simple but: Using a variable in R1C1, here's the attempt:
-----------------------------
Dim RS_Period As Double
-----------------------------
RS_Period = Sheets("Sheet1").Range("B17") - 1
-----------------------------
Range("E2:E" & LastRow).FormulaR1C1 = "=(RC[-2]-OFFSET(RC[-2],9,0))/OFFSET(RC[-2],9,0)"
-----------------------------
I lieu of the "9" I need to use the "RS_Period" which is an offset value.
View 4 Replies
View Related
May 29, 2013
I need to know how to use a variable in an R1C1 statement. The statement is as follows:
Set rng = Sheet1.Range("A2:QFinalRowII").CurrentRegion
Ideally the statement would read something like,
Set rng = Sheet1.Range("A2:Q10").CurrentRegion
How would I use a variable name like "FinalRowII" instead of a number?
View 8 Replies
View Related
Mar 15, 2013
My current dataset goes to row 256, when I use the Macro Recorder it produces the following "static" code.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"claim_export!R1C1:R256C23", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion12
QUESTION/PROBLEM:
Each month the amount of rows could be different (columns should be the same)... I have tried (3) different ways to replace the 256 with my variable name called "numbers"
SourceData:="claim_export!R1C1:R " & numbers & "C23" OR
SourceData:="claim_export!R1C1:R[" & numbers & "]C[23]" OR
SourceData:="claim_export!R1C1:R[" & numbers & "]C23
They all produce the same resulting error:
Run-time error '5': Invalid procedure call or argument.
Note* I am sure my variable is working, because when I "step into" (F8) my code and hover over my variable I can see it showing the number I expect.
View 1 Replies
View Related
May 19, 2007
I am trying to figure out how to use a variable as a row number for use in a range name and/or a pivot table range. Right now I have a range of R571C17, but the row number will change with each use of the pivot table formation macro.
View 3 Replies
View Related
Aug 17, 2009
I have a scenario where my VBA code checks the value of a cell, and then depending on the value the address of another cell is stored. I then setup some conditional formatting, and had been using that stored address as a reference.
View 4 Replies
View Related
Oct 8, 2009
In column B, I have a series of named ranges. The named ranges can vary in order. If I wish to reference a range from a named range say Range("I") to the next unkown named range,
View 9 Replies
View Related
Aug 23, 2007
I am attempting to create a rather complex macro to format periodic raw data being made available to me; this data centers around a main column. The column of interest in these raw data reports is of variable length; I wish to apply a name to this column in order to make it easier to perform calculations. I know how to write VBA code to select every cell in a column down to the point at which the column becomes empty space, regardless of how much data is in the column, and I wish a include naming of the range in the macro. However, all my resources imply you must give the exact definitions of the range when writing the name application in VBA. Does anyone know if there is a way to include naming of a range in a macro without knowing the range's exact size? Put another way, is there any way to apply a name to an already-selected group of cells without giving the exact dimensions?
View 2 Replies
View Related
Oct 29, 2008
I have fought with this for 2 days and I am trying to have a worksheet cell update the date and time if the cell it is referencing (B3 see below)
Here is the formula I am using however it updates every time a new row is added to the worksheet. So the Date/Time stamp on each row is always updated to NOW() everytime.
=IF(ISNA(B3),"0/0/00 00:00",NOW())
I would like to have a Date/Time stamp stay as is once that row has been added. Is there anyway to stop the field from updating once the date and time have been set?
View 9 Replies
View Related
Jan 25, 2013
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup
MsgBox Range(thisString).Address
Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)
View 1 Replies
View Related
Nov 30, 2008
The problem exists with the lists I use. I have a "settings" sheet, where users can choose from preset values to run in the program. E.g. "Urgent", "Semi Urgent" and "Non Urgent" (there are a lot more values, but those will do for now." and the idea is to run them 1 at a time.
However, I've now determined that 99% of the time, they're going to be run 1 after another, that is to say run the program once for "Urgent", then again for "Semi Urgent" and then again for "Non Urgent". So I'd like to loop through these. Again, not a problem, you'd think.
So I added a new value to the list; "All" which users can select to invoke a list. Again, not a problem, I already have the list as a named range, "DmgList" and I can cycle through it. I ran the following test code to see if it would work in theory...
View 4 Replies
View Related
May 11, 2007
I'm trying to highlight a named dynamic range with background colour yellow - basically moving the yellow section of spreadsheet as the dynamic range changes.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oldrang As Range
Range(oldrang).Interior.ColorIndex = Null
Range("named").Interior.ColorIndex = 6
End Sub
View 8 Replies
View Related
Sep 28, 2009
I'm trying to write a vlookup where both the lookup value and the table_array are variables. What I'm trying to do is get a set of dependent drop down boxes to serve as the input for vlookup. The first drop down box contains a list of named ranges and the second contains a list of possible search values. If I manually input the name the function works, but if I refer to it with a cell reference or the indirect funtion it fails. For example:
View 2 Replies
View Related
Apr 14, 2006
I copy a table from a data dump dbf file into worksheet 1. I enter a separate column titled "Comments" and use this for status purposes. My problem is that when I refresh the worksheet, my comments don't stay attached to the rows/ records that they were originally entered for.
View 9 Replies
View Related
Nov 18, 2013
I am trying to use conditional formatting to basically unhide a named range when a specific text is input into a range of cells from C23:C32. I have taken the named range "Screw_Test" and changed the formatting to make it hidden. White text on white background, that sort of thing. What I would like is to change the background color and text color back to something visible when the following is met. In C23:C32 I have a drop down. When Pedicle screw is picked in any of the afore mentioned cells, it would trigger the conditional formatting that would change the "Screw_Test" range (D22:F32) as a whole, making it visible. I haven't had much luck.
View 4 Replies
View Related
Mar 15, 2013
I have a spreadsheet that keeps track of the equipment we have on rent. Whenever we have a new rental, I insert a row and enter the information. I have several conditional formats I am applying - (1) making the font a light grey so that it is hard to see on any rental that has been called off, but invoicing isn't complete on, (2) making the font red on any item that is within 3 days of the term, and highlighting any row that isn't showing an invoice in over 45 days. When anything is completed (rental has been called off, and the final invoice has been approved), I cut the row out and put it in a sheet entitled "Closed Equipment". When I cut out closed records or add new records, it will occasionally mess up my conditional formatting by only referencing one row. Additionally, I don't want the conditional formatting to transfer over to the "Closed Equipment" tab.
Would this be easier to keep clean and straight if I used VBA? Or should I just continue to occasionally check my conditional formatting and clean it up? I thought if I used a Named Range instead of cell references, it might work better, but entering named range "On_Rent" converts to the cell range ($A$4:$AA$194).
I am using Windows 8, Excel 2013. The file is on a network drive, and others in the office can look at it (they all run Windows 7, and either Excel 2010 or 2007), but they look at it so seldom.
View 1 Replies
View Related
Apr 22, 2013
A lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:
Calculate the length of the data set:
Code:
=COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))
(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.
Example named range for the data in column A:
Code:
=OFFSET(INDIRECT("'Data Sheet'!"&"$A$1"),1,0,DSROWCOUNT-1,1)
I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.
The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.
View 7 Replies
View Related
Mar 6, 2013
I'm trying to create a border for a range using R1C1 and get an error.
VB: Sheet11.Range(Cells(y, 1), Cells(y, 5)).BorderAround Weight:=xlThin
If I do not use R1C1 it works...
VB: Sheet11.Range("A18:E18").BorderAround Weight:=xlThin
View 2 Replies
View Related
Oct 31, 2008
I'm trying to select a range of cells using the R1C1 notation. But I'm making an error in the syntax. I know it's really simple, I just don't know what's wrong.
View 2 Replies
View Related
Sep 6, 2006
I am needing to write a line of VB code for a macro that will insert a VLookup formula into a cell where the "named" table_array can be a variable. Example of what I am looking at below.
Worksheets("active Worksheet").("active cell").Formula =VLOOKUP(G2,variable,6,False)"
I need it to be imputed in the active cell of the active sheet with the variable able to be gathered possibly from a cell reference. Say the cell c3 on the active sheet says V080606, the formula imputed would be =VLOOKUP(g2,v0806,6,false).
View 4 Replies
View Related
Jan 29, 2007
i have 1 - 9 number buttons, the +, - , * , / , and a C for clear, as well as an exit button. the display is a label. i have to click add after inputting the digits just to have it added to he variable, which i just add up at the end... terrible.
Option Compare Database
Option Explicit
Dim var1 As String
Dim var2 As String
Dim first As String
Dim second As String
Dim sign As String
' on load
Private Sub form_load()
Form.Caption = "this calculator lab sucks."
Me.lbDisplay.Caption = ""
End Sub
' on clear
Private Sub cmdClear_Click()
Me.lbDisplay.Caption = ""
var1 = ""
var2 = ""
End Sub.............
View 3 Replies
View Related
Apr 29, 2009
From the sample file, I am using the data on (start) worksheet. (Sort and format sheet) . This one is way over my head. After new data is added to the sheet via another UserForm, I want to sort the list by name and have alternating backgrounds based on grouped unique names.
View 2 Replies
View Related
Dec 10, 2006
Cell b6 has been formatted to a percentage with 2 decimal places. Using the formula: ="todays percentage is "&B6&", thank you.". The value returned is: todays percentage is 0.78125 thank you. My question is can the returned value from cell b6 be formatted to a percentage or at least be formatted to 2 decimal places? I hope i posted this correctly i am a first timer.
View 2 Replies
View Related
