Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting to the bottom of it
I’m reposting my question since I messed up on the phrasing the first
time. I need to figure out how to get to cell E154 which is the last cell in a number of rows, But, which will be changing, because the number of rows will be changing every time I create this spreadsheet. If I start at Cell E3 and then put in code to get to the very last cell (in this case, it just happens to be E154), I'm assuming it would be something like the following: Dim Rng As Range Set Rng = Range("E3").End(xlDown) But, once I have this range, I'm not sure of the code to actually get me there using this range Then, once I’ve gotten to that last cell (E154) I have the following vb code to copy this footer (which was used to sum the contents of the column above it) and paste it to the 6 cells to the right. This is what I have so far but I need to make it relative from the starting point. Range("E154").Select Selection.Copy Range("F154:L154").Select ActiveSheet.Paste Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting to the bottom of it
On 27/07/2010 22:11, tbmarlie wrote:
I’m reposting my question since I messed up on the phrasing the first time. I need to figure out how to get to cell E154 which is the last cell in a number of rows, But, which will be changing, because the number of rows will be changing every time I create this spreadsheet. If I start at Cell E3 and then put in code to get to the very last cell (in this case, it just happens to be E154), I'm assuming it would be something like the following: Dim Rng As Range Set Rng = Range("E3").End(xlDown) But, once I have this range, I'm not sure of the code to actually get me there using this range Then, once I’ve gotten to that last cell (E154) I have the following vb code to copy this footer (which was used to sum the contents of the column above it) and paste it to the 6 cells to the right. This is what I have so far but I need to make it relative from the starting point. Range("E154").Select Selection.Copy Range("F154:L154").Select ActiveSheet.Paste Thanks! Hi tbmarlie, This is short on explanation: Sub AAA() Dim Firstcell As Range Dim LastCell As Range Set Firstcell = Range("E3") Set LastCell = Cells(Rows.Count, 5).End(xlUp) LastCell.Offset(1, 0) = "=Sum(" & Firstcell.Address(False, False) & ":" & LastCell.Address(False, False) & ")" LastCell.Offset(1, 0).Copy ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(LastCell.O ffset(1, 1), LastCell.Offset(1, 6)) Application.CutCopyMode = False End Sub Ask if there is anything you don't follow |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting to the bottom of it
Option Explicit
Sub testme() Dim wks As Worksheet Dim LastCell As Range Set wks = ActiveSheet With wks Set LastCell = .Cells(.Rows.Count, "E").End(xlUp) End With LastCell.Offset(1, 0).Resize(1, 7).FormulaR1C1 = "=sum(r3c:r[-1]c)" End Sub I like to start at the bottom and work to the top of the worksheet. I avoid empty rows that way. With wks Set LastCell = .Cells(.Rows.Count, "E").End(xlUp) End With Since .offset(1,0) comes down one row. And .resize(1,7) says to resize that original cell to 1 row by 7 columns. Then it applies the same formula (summing the data that starts in row 3 and goes to the cell above the formula) to all these 7 cells. On 07/27/2010 16:11, tbmarlie wrote: I’m reposting my question since I messed up on the phrasing the first time. I need to figure out how to get to cell E154 which is the last cell in a number of rows, But, which will be changing, because the number of rows will be changing every time I create this spreadsheet. If I start at Cell E3 and then put in code to get to the very last cell (in this case, it just happens to be E154), I'm assuming it would be something like the following: Dim Rng As Range Set Rng = Range("E3").End(xlDown) But, once I have this range, I'm not sure of the code to actually get me there using this range Then, once I’ve gotten to that last cell (E154) I have the following vb code to copy this footer (which was used to sum the contents of the column above it) and paste it to the 6 cells to the right. This is what I have so far but I need to make it relative from the starting point. Range("E154").Select Selection.Copy Range("F154:L154").Select ActiveSheet.Paste Thanks! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting to the bottom of it
ps. You have another response to your earlier post, too.
On 07/27/2010 16:11, tbmarlie wrote: I’m reposting my question since I messed up on the phrasing the first time. I need to figure out how to get to cell E154 which is the last cell in a number of rows, But, which will be changing, because the number of rows will be changing every time I create this spreadsheet. If I start at Cell E3 and then put in code to get to the very last cell (in this case, it just happens to be E154), I'm assuming it would be something like the following: Dim Rng As Range Set Rng = Range("E3").End(xlDown) But, once I have this range, I'm not sure of the code to actually get me there using this range Then, once I’ve gotten to that last cell (E154) I have the following vb code to copy this footer (which was used to sum the contents of the column above it) and paste it to the 6 cells to the right. This is what I have so far but I need to make it relative from the starting point. Range("E154").Select Selection.Copy Range("F154:L154").Select ActiveSheet.Paste Thanks! -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting to the bottom of it
On Jul 27, 2:30*pm, ManicMiner17 wrote:
On 27/07/2010 22:11, tbmarlie wrote: I’m reposting my question since I messed up on the phrasing the first time. I need to figure out how to get to cell E154 which is the last cell in a number of rows, But, which will be changing, because the number of rows will be changing every time I create this spreadsheet. *If I start at Cell E3 and then put in code to get to the very last cell (in this case, it just happens to be E154), I'm assuming it would be something like the following: Dim Rng As Range * * *Set Rng = Range("E3").End(xlDown) But, once I have this range, I'm not sure of the code to actually get me there using this range Then, once I’ve gotten to that last cell (E154) I have the following vb code to copy this footer (which was used to sum the contents of the column above it) and paste it to the 6 cells to the right. This is what I have so far but I need to make it relative from the starting point. Range("E154").Select * * *Selection.Copy * * *Range("F154:L154").Select * * *ActiveSheet.Paste Thanks! Hi tbmarlie, This is short on explanation: Sub AAA() Dim Firstcell As Range Dim LastCell As Range Set Firstcell = Range("E3") Set LastCell = Cells(Rows.Count, 5).End(xlUp) LastCell.Offset(1, 0) = "=Sum(" & Firstcell.Address(False, False) & ":" & LastCell.Address(False, False) & ")" LastCell.Offset(1, 0).Copy ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(LastCell.O ffset(1, 1), LastCell.Offset(1, 6)) Application.CutCopyMode = False End Sub Ask if there is anything you don't follow- Hide quoted text - - Show quoted text - The compiler doesn't seem to like the := after Destination. Specifically, it is saying Compile error: Expected Expresssion. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting to the bottom of it
On Jul 27, 2:47*pm, tbmarlie wrote:
On Jul 27, 2:30*pm, ManicMiner17 wrote: On 27/07/2010 22:11, tbmarlie wrote: I’m reposting my question since I messed up on the phrasing the first time. I need to figure out how to get to cell E154 which is the last cell in a number of rows, But, which will be changing, because the number of rows will be changing every time I create this spreadsheet. *If I start at Cell E3 and then put in code to get to the very last cell (in this case, it just happens to be E154), I'm assuming it would be something like the following: Dim Rng As Range * * *Set Rng = Range("E3").End(xlDown) But, once I have this range, I'm not sure of the code to actually get me there using this range Then, once I’ve gotten to that last cell (E154) I have the following vb code to copy this footer (which was used to sum the contents of the column above it) and paste it to the 6 cells to the right. This is what I have so far but I need to make it relative from the starting point. Range("E154").Select * * *Selection.Copy * * *Range("F154:L154").Select * * *ActiveSheet.Paste Thanks! Hi tbmarlie, This is short on explanation: Sub AAA() Dim Firstcell As Range Dim LastCell As Range Set Firstcell = Range("E3") Set LastCell = Cells(Rows.Count, 5).End(xlUp) LastCell.Offset(1, 0) = "=Sum(" & Firstcell.Address(False, False) & ":" & LastCell.Address(False, False) & ")" LastCell.Offset(1, 0).Copy ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(LastCell.O ffset(1, 1), LastCell.Offset(1, 6)) Application.CutCopyMode = False End Sub Ask if there is anything you don't follow- Hide quoted text - - Show quoted text - The compiler doesn't seem to like the := after Destination. Specifically, it is saying Compile error: Expected Expresssion. *Thanks Try this: Sub Copy_To_Right_v2() Const colStr = "E" 'You assign column letter. Dim rBeg As Integer, rEnd As Integer Dim rng As Range rBeg = 2 'You which row to start. rEnd = Cells(Rows.Count, colStr).End(xlUp).Row Set rng = Range(Cells(rBeg, colStr), Cells(rEnd, colStr)) rng.Copy rng.Range("B1:G1") rng.Range("B1:G1").EntireColumn.AutoFit End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting to the bottom of it
Hi tbmarlie,
Sorry I didn't get back before, just got in: This is all one line which should explain the syntax error: ActiveSheet.Paste _ Destination:=Worksheets("Sheet1").Range(LastCell.O ffset(1, 1), _ LastCell.Offset(1, 6)) Having said that I saw the code Dave Peterson posted; much more elegant than mine and shorter to boot! I'd use that especially as he explained it well. Good Luck. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Top 5 vs Bottom 5 | Excel Discussion (Misc queries) | |||
Top 10% and Bottom 10% | Excel Worksheet Functions | |||
dislike jump bottom of column by double-clicking the bottom of cel | Excel Discussion (Misc queries) | |||
Top to bottom | Excel Discussion (Misc queries) | |||
how to paste data from top to bottom to bottom to top | Excel Discussion (Misc queries) |