Naming A Range Of Variable Length
Feb 5, 2007
I have a range of data starting in C18, C17 is a label (DivsUsed) the number of rows can range from 1 to a few hundred.
I used the code below to name C18 onwards as a named range with the name DivsUsed and then to use this as a RowSource for a ListBox.
Private Sub UserForm_Initialize()
Dim rangeToName As Range
'Sheet2.Range("C18", Range("C65536").End(xlUp)).Name = "DivsUsed" Tried this too
Set rangeToName = Sheet2.Range("C17", Range("C65536").End(xlUp))
rangeToName.CreateNames Top:=True
ListBox2.RowSource = "DivsUsed"
TextBox2.Value = Sheet2.Range("F2").Value 'This works ok
End Sub
Both tries, and anything else I have tried, give the error " Method 'Range' of object _Worksheet failed".
View 7 Replies
ADVERTISEMENT
Mar 13, 2012
I am trying to name a Range in VBA with the following Case statement.
Select Case counter
Case "2": Set Wk(numWeeks) = Range("P1:Q136")
ActiveWorkbook.Names.Add Name:="Wk" & numWeeks, RefersTo:=Wk(numWeeks)
I have many more cases and have defined Wk(5) as a Range. What I am really after it is possible to to name the range using something similar to the code above that uses a predefined variable in the naming process.
View 4 Replies
View Related
Aug 28, 2009
I require vba code which will allow me to select a range of variable size.
Starting from cell A2 use :
Range(Selection, Selection.End(xlDown)).Select
However then using the line below may not select the entire range as some columns may be empty
Range(Selection, Selection.End(xlToRight)).Select
View 9 Replies
View Related
Apr 30, 2009
I have an issue regarding use of conditional formatting where the range i wish to format is on variable length.
For example; lets say i want to format F15 downwards depending on there being a value in the cell, how could I do that ?
Lets say I want the cell to turn GREY if number is 0, RED if the number is below 85, GREEN if above 95 and AMBER otherwise.
How could I do that ?
Any how could i ensure it picks all rows in the range where numbers have been added
View 7 Replies
View Related
Apr 21, 2013
I've recorded this Macro to sort the cells in a column alphabetically if any cells have content.
I would like to use the Macro on the whole workbook however the AB column range varies between the worksheets.
How should I adapt this Macro to sort simply to the END of AB column??
' PWRII Macro
'
'
Cells.Select
[Code]....
View 5 Replies
View Related
Jan 18, 2008
I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
View 2 Replies
View Related
Jan 9, 2010
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
View 9 Replies
View Related
May 24, 2012
How do I name a Variable for a formula in VBA? Example: I want to name the results of Cells A1 + A2 = X. Then I want to add C1 + X = and make that results a variable less say Y
I have a very large formula and if I can name varaibles it would be much easier.
View 3 Replies
View Related
Oct 19, 2006
I have a sheet which is using a single input value to calculate an output. On the other side of the sheet, I am using goalseek to find what input value would give me a specific output value - this is pre-set.
Starting in row 12, I am using the macro to paste the goalseek solution into a new column at the end and then resetting the original input value. The macro then moves onto row 13, repeats and loops and so on.
My questions are:
1) how can I get the macro to count the number of rows with data in it instead of fixing it at 217 (as seen in "For x= 12 To 217")?
2) how can I get the goalseek goal to reference a user defined cell instead of being fixed at 37 (as seen in "Cells(x, 13).GoalSeek Goal:=37, ChangingCell:=Cells(x, 5)")?
3) after it fills col15 with the goalseek solution to 37 for all the rows, how can I then start a new loop to tell it to start filling col16 with the goalseek solution to say 50, and then col17 with the goalseek solution to say 55? ..
View 4 Replies
View Related
Jun 20, 2014
So I have got an identifier and a corresponding column of values. I need to calculate sum of those corresponding values for each identifier, so if identifier is 1993, I need to have a sum of all the corresponding values. These identifiers repeat, and I do not want to calculate sum for all of them, just the immediate identifiers (i.e. you have 1993, after that you may have 1994 etc etc and then at one point you will have 1993 again, but that "new" 1993 will have a different sum of course). Also, identifiers do not go in sequence sometimes (however, most of the case they do), for example 1993 may jump suddenly to 2004. The trick with all of this is variable "row width" of these identifiers...
222.xlsm
View 13 Replies
View Related
Jul 26, 2013
how can I make this simple macro sum columns that will be of variable length? I would like the sum to appear as the last entry in that column and highlighted.
VB:
Range("H36").Select
Selection.FormulaR1C1 = "=SUM(R[-34]C:R[-1]C)"
Range("J36").Select [code]....
View 7 Replies
View Related
Jun 10, 2014
Column M:
##/##/#### | Variable Length Text-####
Example:
01/06/2014 | Daniel Trimble-4048
I need to parse out the different parts of Column M.
In Column R -- "Close Date", I'm successfully using:
=LEFT(M2,FIND(" | ",M2)-1)
...to extract the close date of the donation.
In Column S, I want to list the donor name--which is all of the text after " | ", and before the "-".
I don't need anything after the hyphen, and fortunately in this data, no one's name has a hyphen in it.
The Close Date is working fine for the LEFT and FIND functions, but for the life of me, I can't seem to get MID to work for the variable-length text. The text will always start in the same position -- 14, as the date and delimiter are standardized. And the last 5 characters of the text are not variable in length, so they can be cut out completely.
How do I use MID to extract everything starting at position 14, and stopping 5 characters short of the end of the text?
View 12 Replies
View Related
Feb 9, 2014
i just wanna extract the date shown(last data until meet the first comma) in first column to another column.
column 1
abcde, 1234
abcd, 12345
abcdef, 123
column 2
1234
12345
123
View 3 Replies
View Related
Jan 22, 2006
I have a SS I would like to reference a cell in a column which changes in
length. In this case, I would like to reference the most current date in a
column.
Is there a simple method for doing this, as the data will routinely be
different lengths in that column.
Further, there is Data on the Horizontal that I would like to reference to
another cell in another sheet that would correspond to this date. Can anyone
give any suggestions as to the best way to accomdate this?
View 11 Replies
View Related
Apr 10, 2007
I am trying to do is to build an array from a series of cells that may or may not contain text.
For example cells A1:A6 may be equal to red, orange, yellow, green, blue and purple,
but each cell may also be blank.
So I might also have blank, blank, yellow, blank, blue, purple.
Is there a way I can make an array of just {yellow, blue, purple}? and then the next time it may be {red, blue}
Or can I index only cells that have text?
In the end, I just need a way to make a selection from only those cells that have text.
View 9 Replies
View Related
Feb 10, 2009
I'm trying to sort a report using case number in Ascending order, this report varies in length and data begins in cell B2 down the whole report. It needs to include the fields on either side ie. expand the selection.
View 3 Replies
View Related
Mar 28, 2006
I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.
For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.
I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)
but it assumes some level of consistency, the "-" in the cell.
View 18 Replies
View Related
Dec 12, 2012
My key is in row A (up to 50,000 keys) my entries are in column B. There could be between 1 to 20 entries per key (variable). i need to move the entries across so my key remains in column A and the entries appear in columns B, C, D, etc. Need to end with one row per key.
from this:
823
car
pool
window
345
pen
123
pen
[code].....
View 2 Replies
View Related
May 24, 2008
If you have used Excel to download data from Bloomberg, you probably noticed that the function "BDH" can return an array of variable length. I was trying to design such a function but could not find a way.
The basic feature of a function like this is that it can be entered like an ordinary formula (not an array formula), but it returns multiple values. The values will be displayed in the range that has the formula as its top left corner.
View 9 Replies
View Related
Jan 13, 2009
Hey I got a long String like this "[...] increase of x.xx% [...]".
I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.
I tried this formula:
=MID(G14,SEARCH("%",G14)-5,5)
but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.
View 9 Replies
View Related
Aug 2, 2012
I'm trying to sort by a column with one to four digit numbers, any of which may be followed by a single letter, i.e. 1,2,3 4A,5, 10,11A,75,101A,600,705,1010B,1011A.
I'd like them to sort in the above order. I am only able to have excel 2003 sort all of the numeric cells first, followed by the cells with the alpha character.
View 4 Replies
View Related
Jan 25, 2007
Imagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".
Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.
Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.
View 5 Replies
View Related
Jun 30, 2014
my macro comes up with an error on the Consolidate line that says 'Cannot add duplicate source reference'. I have a workbook with a variable number of tabs and I want to consolidate-sum (not copy and paste) the data from the various sheets onto a summary sheet within the same workbook. I'm at the point where it all works fine until it hits Consolidate.
Option Explicit
Sub ConsolidateExport()
'
' ConsolidateExport Macro
[Code]....
View 1 Replies
View Related
Jun 6, 2007
I want to use the range in this code that works fine in one work book from sheet to sheet...
But i need it to run across 2 workbooks that are both open at the same time... The data its bringing back will be in a seperate workbook.
Sub zzzz()
Sheets("Sheet2").Select
Range("A3:M16").ClearContents
c = 3 'Paste Range Return Test
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'data source
For a = 1 To x
If Worksheets("sheet1").Cells(a, 3) = Worksheets("sheet2").Range("a1") Then
For b = 1 To 20
Cells(c, b) = Worksheets("sheet1").Cells(a, b)
Next b
c = c + 1
End If
Next a
'Range("C3:C15,F3:F15,I3:I15").Select
'Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
View 9 Replies
View Related
Jan 31, 2007
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.
Set 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))"
View 5 Replies
View Related
Mar 26, 2008
After pasting a Range and while the Range is still selected, I would like to Name it. May I have some assistance with the proper statement?
View 9 Replies
View Related
May 16, 2006
I am making a simple database worksheet for the junior. The first thing we need to re-define the new "size" of the database to do some future calculations. I write some code in workbook open to initiate the name area:
Worksheets("Inventory").Activate
maxrow = 0
maxrow = Range("a7"). CurrentRegion.Rows.Count
'Range("A7").Select
Range(Cells(8, "N"), Cells(maxrow, "N")).Name = "tbl_left"
when the junior re-open the workbook after closed, the size of the database
does not growth to a new area even junior add rows of data before close. It seems the currentregion does not work, I've tested it in VBE by press " F8".......
View 5 Replies
View Related
Jun 15, 2006
= OFFSET(CustomerInfo!$A$9,0,0, COUNTA(CustomerInfo!A:A),1)
for a "dynamic" range name...i like the concept, but my data validation in a cell that references this "dynamic" range name (with the offset function) doesn't recognize all the values in the range
(when i just range name it, the data validation sees all the names, but not all when i use the offset...)
View 5 Replies
View Related
Mar 15, 2007
I am trying to name a selected range in VBA in order to use it in a vlookup table. Code I have so far is as follows:
ActiveSheet.Name = TextN
Range("c9"). CurrentRegion.Select
Call formatting
ActiveWorkbook.Names.Add Name = "formTextN", RefersToR1C1 = Worksheets(TextN) & "!" & "r9c2:r45c13"
The formatting works, but the selection does not get named.
View 3 Replies
View Related
Oct 12, 2013
I am having a hard time creating my ultimate gradebook. Right now, I am able to hide a SPECIFIC table on a SPECIFIC worksheet using the following code on a macro button:
VB : Sub HideRow()Range("Table2[#All]").EntireRow.Hidden = True End Sub
The problem is that this sheet will be a template and as a new user inserts a new sheet, I would like to copy and paste this template to each new sheet. Since the Table values change with each new sheet, code wont work on the new tables. How I can name each new sheets table "Class Data", and the macro button to hide will work on each respective sheet?
View 2 Replies
View Related
