Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
last row of sheet1 to use in a formula in sheet2
I would like to use the last row number as a value in the last row of a range.
For example, the following formula will determine a row number that I can display in a message.: LRow = ThisWorkbook.Sheets("sales").Cells(Rows.Count, 1).End(xlUp).Row Now, on another worksheet in the same workbook, I have a macro that at some point has the following line: Range("A1:E1").Select Selection.AutoFill Destination:=Range("A1:E201"), Type:=xlFillDefault I would like the LRow value to be inserted in place of the 201 in the range above. I tried Range("A1:E(SLRow)") but that didn't work. This must be easy, but maybe I am tired and so it eludes me. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
last row of sheet1 to use in a formula in sheet2
Anything included between quote marks in a string value is considered as
text... your SLRow is nothing more than 5 characters of text where you put it. In order to get the variables *value* inserted at the location you indicated, you have to concatenate it there. Try this... Range("A1:E(" & SLRow & ")") Now VB will see SLRow as the variable and will substitute its value at that location. -- Rick (MVP - Excel) "Solutions Manager" wrote in message ... I would like to use the last row number as a value in the last row of a range. For example, the following formula will determine a row number that I can display in a message.: LRow = ThisWorkbook.Sheets("sales").Cells(Rows.Count, 1).End(xlUp).Row Now, on another worksheet in the same workbook, I have a macro that at some point has the following line: Range("A1:E1").Select Selection.AutoFill Destination:=Range("A1:E201"), Type:=xlFillDefault I would like the LRow value to be inserted in place of the 201 in the range above. I tried Range("A1:E(SLRow)") but that didn't work. This must be easy, but maybe I am tired and so it eludes me. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
last row of sheet1 to use in a formula in sheet2
Just a typo that was in the original that was carried over in your response:
Range("A1:E(" & SLRow & ")") should be: Range("A1:E" & SLRow) or Range("A1:E" & LRow) I'm know that the ()'s shouldn't be there. I'm confused by the SLRow/LRow stuff, though. Rick Rothstein wrote: Anything included between quote marks in a string value is considered as text... your SLRow is nothing more than 5 characters of text where you put it. In order to get the variables *value* inserted at the location you indicated, you have to concatenate it there. Try this... Range("A1:E(" & SLRow & ")") Now VB will see SLRow as the variable and will substitute its value at that location. -- Rick (MVP - Excel) "Solutions Manager" wrote in message ... I would like to use the last row number as a value in the last row of a range. For example, the following formula will determine a row number that I can display in a message.: LRow = ThisWorkbook.Sheets("sales").Cells(Rows.Count, 1).End(xlUp).Row Now, on another worksheet in the same workbook, I have a macro that at some point has the following line: Range("A1:E1").Select Selection.AutoFill Destination:=Range("A1:E201"), Type:=xlFillDefault I would like the LRow value to be inserted in place of the 201 in the range above. I tried Range("A1:E(SLRow)") but that didn't work. This must be easy, but maybe I am tired and so it eludes me. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
last row of sheet1 to use in a formula in sheet2
You were right and the answer is perfect. As for the other, this is me not
typing well. SLRow and LRow should have been the same. Thank you for your help and patience. This forum is an oasis in a desert. "Dave Peterson" wrote: Just a typo that was in the original that was carried over in your response: Range("A1:E(" & SLRow & ")") should be: Range("A1:E" & SLRow) or Range("A1:E" & LRow) I'm know that the ()'s shouldn't be there. I'm confused by the SLRow/LRow stuff, though. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated | Excel Programming | |||
need a formula to transpose sets of data from Sheet1 to Sheet2 | Excel Discussion (Misc queries) | |||
formula-add 2 numbers of cell a1 and a2 of sheet1 ans-b9in sheet2 | Excel Worksheet Functions | |||
Add a formula to sheet2 for to each nonempty line in sheet1 | Excel Programming |