Selecting Range 6 X The Length Of Another Column?

May 15, 2012

I have a worksheet which already has some built in formulas which take the data in columns V and W, and use them to build other coding in column P. The trick is that the coding created in column P will be six times as long as the source data in columns V and W.

I.e., a single row containing "Sample1" and "Sample 2" in columns V and W respectively create the six following rows in column P:

Row 1:
Row 2: TMUnknown
Row 3: tested1
Row 4: Sample1
Row 5: Sample2
Row 6:

I want to be able to select the accurate length of Column P, which should be 6x as long as columns V or W. Any dynamic way to do this? (Since the amount of data pasted into columns V and W will change each time I use this worksheet.) I know how to select set ranges, but not how to adjust them as multiples of the length of another column

View 4 Replies


ADVERTISEMENT

Selecting Range With Variable Length

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

Adapting Macro For Variable Column Length / Range END

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

Selecting Range Of Rows In Column

Sep 11, 2013

I want to select a range of rows 11, 14 and 23 to 24 in column j, plus the 2 rows to the right of column J and run a sum formula on the range. My procedure is selecting rows 11/14/23/24 and doing the sum for only column J. How do I make it so it runs the sum formula on all 3 columns. My code is copied below. I am fairly new to VBA.

Code:
With ActiveSheet.Range(Cells(27, j), Cells(27, j)).Select
Set sumRng = Intersect(Range("15:21, 25:25"), Columns(j)).Offset(0, 2)
ActiveCell.Value = WorksheetFunction.Sum(sumRng)
End With

View 4 Replies View Related

Selecting The Whole Column As Range In Macro

Jul 17, 2008

I am working on a VBA macro, using the following

With wsSheet.Range("A:A")
ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
MyArray = wsSheet.Range("A:A")
For i= 1 To .Rows.Count
For j= 1 To .Columns.Count
wsSheet.Cells(i, j+ .Offset(0, 3).Column) = MyArray(i, j)
Next: Next

I want to select alla values in column A, but when I specify the range as "A:A" the code results in an infinite loop! How can I come around this?

View 9 Replies View Related

Selecting A Variable Column Range

Jun 21, 2006

I have a workbook that generates sheets for each year based on selected criteria. It starts at Column H and goes too AH and beyond. When my loop reaches Z it errors out. I think this is happening because the code is referencing the column as ASCII. Here is the

Sub Test()
Dim d As Date
Dim yrint, i, num_years, fields, field_start As Integer
Dim yrstr, crit1, crit2, left_column_range_fixed, right_column_range_fixed, left_column_range_var, right_column_range_var, left_column_range, right_column_range, cost_column, cost_column_var, cost_column_fixed As String
left_column_range_fixed = "H"
right_column_range_fixed = ":AH"
cost_column_fixed = "2"
crit1 = "=x"
crit2 = ">0"
d = Date
yrint = Sheets("Overall"). Range("H2")
field_start = 9 'changed from 9....................

View 4 Replies View Related

Selecting The 2 Last Values In A Column Of Data With Range

Jan 23, 2007

I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....

View 9 Replies View Related

Excel VBA - Selecting Dynamic Range From Multiple Column Data

Feb 11, 2014

Selecting the range from Multiple Column data.

Currently, it is:

[Code]....

I have data from columns A:E

View 4 Replies View Related

Selecting All Data In Specific Columns Without Selecting Adjacent Column

Mar 10, 2014

Using VBA, I need to Select A1:C14.

The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.

So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)

Obviously, this is an example...the real data set is an export and varies in size.

View 1 Replies View Related

Shorten String Length If Exceeds 31 Characters (max Sheet Name Length)

Mar 27, 2014

I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0

Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?

[Code] .....

View 2 Replies View Related

Formula Code To Find Arc Length From Chord Length

Sep 4, 2012

How to create a code formula to calculate the arc length from a given chord length?

If you know the radius of the major circle.

Say the chord is 50mm and major circle dia is 72mm (radius 36mm)

arc from chord.jpg

View 5 Replies View Related

Give Cell A Value Based On Length Of Row (variable Row Length)

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

SUM A Column With Unknown Length

Jun 26, 2012

Trying to sum up a column that has an unknown length. This is a canned Excel form that is saved within a software system and is used to create customized documents. Usually, I would enter the table name.field name in a cell to pull the data from various parts of the system.

In this case, that data resides in AA234 of my customized Excel sheet. When the user retrieves this doc from the system, the data could run several rows, starting from AA30 to AA255. So, in my "Total" cell, I've entered =SUM(AA1:AA255), so it will just add up whatever is in the column, regardless of how long it runs upon document generation. When I try to pull the document, I get "=SUM(#REF!)" in that field instead.

View 9 Replies View Related

Change Range Length Programmatically?

Aug 19, 2013

I have a workbook that contains a form with cells that use choice list validation. It also contains a worksheet with all the choice lists. Each choice list is a named range. The form is used to collect data that is new and from historical records. The historical records contain entries that are no longer allowed in new data. To allow for the older data I have choice lists (named ranges) that contain both the new and obsolete choices. This is a data problem waiting to happen. I would like to be able to have the user tell the workbook if the data is new or historical. If new I would only provide them with current choices. If historical, I would provide them with all choices, current and obsolete.

I would link the VBA script to change expand the ranges when told the data being entered is historical. The problem is I don't know how to code such a script. For example one of the cells has as current choices W, M, D. The range would be named MoistureState and the range would be A2:A4. There is one obsolete choice, E. If the Historical data button was clicked the MoistureState range would change from A2:A4 to A2:A5.

How would one go about expanding the named range?

View 8 Replies View Related

Find The First Non Zero Length Cell In A Range

May 5, 2009

I have date in a range from a1 to cw.... this could range to the whole worksheet as it varries.

Now i started to write the macro by inserting three rows at the top of the sheet, no problem, now how do i make cell a5 become the new active cell so that i can freeze pane .

so i really need to do the "find the first non zero length cell in a range" code as I know it will always be a5. this part will not change.

I cant seem to find how to change the active cell from a1 to a5....

View 9 Replies View Related

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 View Related

Edit Length Based On Other Column

Oct 28, 2011

I have two columns, one with an account number (call it A) and another with a vendor id (call it B). I need the length of the number in column A to be truncated to 10 characters by just deleting any additional characters after 10. That should be based on whether or not the corresponding cell in column B equals the following number "147212336". Right now, my code edits the ENTIRE column A, not just the cell in the same row.

I know it's because I'm using a "FOR EACH CELL" argument but I don't what else would work.

Code:
Set acctlengthrange = Range("N2", Cells(LastRowB, "N"))
Set dplrange = Range("P2", Cells(LastRowB, "P"))
With dplrange
If cell.Value = "147212336" Then
For Each cell In acctlengthrange
cell.Value = Left(cell.Value, 10)
Next
End If
Next

View 6 Replies View Related

Select Column Of Unknown Length Using VBA

Jul 14, 2014

I have a column that begins in the same place all of the time (Cell C6) however the length of this column can vary. How can I copy the column using VBA?

View 2 Replies View Related

Cut Text Data To Length Down Column

Dec 25, 2006

I have a range of text data in a column and need to get the text lengths to no longer than 60 characters.

The remaining data then need to be copied in a cell inserted below the original.

I have been playing with the following code…

View 9 Replies View Related

Isolate The Width And Length Of Column

Feb 17, 2009

Hello All:

I have the following type information in thousands of cells. I need too isolate the Width and Length from these cells. The width and the length are the two numbers on each side of the "X".

RAPITONE C2 10 X 10 100
RAPITONE M2 10 X 10 250
RAPITONE M2 10 X 10 250
RAPITONE M2 10 X 10 250
RAPITONE M2 10 X 10 250
RAPITONE M2 10 X 10 250
RAPITONE M2 10 X 10 250
RAPITONE M2 76 EI 42 X 100 ROLL

View 9 Replies View Related

Conditional Formatting Of Range Of Variable Length

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

Character Length Limit In Column In Excel?

Dec 16, 2013

Formula on how to limit character length limit in a column in excel. I would like to limit the column to 32 characters

View 2 Replies View Related

Computing With Last 252 Rows Of Varying Length Column

Nov 26, 2009

Using Excel 2003, Column E has a varying and unpredictable number of rows, which only the final 252 rows are of significance. I need to compute the following formulas, here written in English as I can't determine the proper terminology to accomplish this task in Excel:

Final Row with a number in Column E/average (final 252 rows with numbers in column E)+

Final Row with a number in Column E/average (final 126 rows with numbers in column E)+

Final Row with a number in Column E/average (final 63 rows with numbers in column E)

Once again, what makes this not straightforward for me is the column may have wildly varying numbers of rows.

View 6 Replies View Related

String In Column A To Be Divided Into 2 Strings If Length > 50

Apr 8, 2013

I have a set of data in Which in column A is the name of organisation.

If string in col A is longer than 50 I need to split in and put in col B.

That would be simple however I need to do it in a smart manner: i.e. cut it to the nearest full word.

Example:

THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS

Incorrect; length = 98

THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT
Correct; trimmed down to 48.

My question is about formula that can detect spaces and depending on those trim the string down adequatly:
to 50 if 50th char is preceeded by space; if not then check where is the next space going towards left. Once you find it cut the string there.

What formula I can use to get desired effect?

View 9 Replies View Related

Converting Different Length Rows Into Single Column

Jul 29, 2014

I have a table in excel in which every line has as many values as the days of the month (e.g. 31 values first row, 28 the second..) and it goes from January 1948 to July 2014. So it ends up having around 800 rows.

I need to put all of those into a column, but it gets hard because the rows have different lengths. I have seen in this forum some solutions that apply well when the table is regular, but I'm stuck in trying to get a solution for when it's not. I have tried to record a macro using TRANSPOSE, but then I cannot change the cells into this formula in the editor in order to create a loop.

I also have to consider leap years.

View 4 Replies View Related

Extract Strings From Range Of Variable Length Text

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

Separate Contents Of Column Of Numbers Based On Their Length?

Mar 5, 2013

I have a spreadsheet with numbers like this

9404388
9404374
9404391
9405695
38301663
9409724
1791016
9435145
9530758
9440464
9441182
71000569
9467438

There are a thousand numbers like this, I was wondering how I can move the cells with 7 numbers to the right column, and keep the cells with 8 numbers where they are (or move them to the 3rd column to the right)

View 14 Replies View Related

Sort Column Of Variable Length Alphanumeric Characters?

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

SUMIF- Column Of Contract Numbers Of Varying Length

Mar 27, 2009

I have a column of contract numbers of varying length. I want to run a sumif of all of the values on my reference sheet that have the contract number beginning with the contract number in my listing. All of the the contract values in my reference sheet are very long. Since the numbers I am working with vary in length, i don't know how to match this string in the sumif function

I want something like this

=SUMIF('Reference Sheet!$A$5:$A$13410,LEFT("", LEN('Select Contracts'!$C6))='Select Contracts'!$C6,'FINALIZED DATA'!$H$5:$H$13410)

Is there a way to refer to the range in the sumif function,

View 9 Replies View Related

Sort Column By Count/length Of Cell Characters

Dec 30, 2008

Ive written the macro below to sort Column A according to the length of the cell values in Ascending order. Ive done this by writing the length to Column B for each value and then sorting on column B. Surely there is a more elegant method, perhaps using arrays.

Sub SortIt()
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Value = Len(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved