Naming a variable cell that changes daily
Posted by chrisB on December 06, 2001 4:19 PM
Ok, so I know this is simple, I just don't kow how to do it ... what I really need to know is how to name the active cell in the copy command when that cell can vary from day to day. Here is an example of the code:
ActiveCell.FormulaR1C1 = "=RC[-1]"
'THIS NEXT LINE IS WHAT I CAN'T FIGURE OUT !
Range("ActiveCell:" & "U:" & LastRow).Select
With Selection
.FillDown
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
THANKS!
Posted by Tom Urtis on December 06, 2001 4:39 PM
Please correct me if I am misinterpreting your question, but are you essentially interested in copying a formula into a dynamic range, to the last used row? It looks like you know where the formula is supposed to be entered, but maybe the number of rows varies from day to day that must contain a copy of that formula? If so, try this code:
Sheets("Your SheetName").Select
Dim LastRow As Long
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Range("B1:B" & LastRow).FormulaR1C1 = "=RC[-1]"
Application.CutCopyMode = False
This code enter your formula in B1 and copy it down to the last used row in your sheet.
Then, I notice in your post that you want to paste special the formulas for values. So add this code:
Range("B1", Range("B1").End(xlDown)).Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Did I guess wrong on the point of your question, or is this any help??
Tom Urtis
Posted by chrisB on December 06, 2001 5:05 PM
Let me try to restate question
Sorry Tom, I do seem to be having trouble asking this question properly. What I have is a macro that filters data and column U data can begin on any row each day. I have a subroutine that identifies the last row, and I use a loop to identify the first data cell in column U. What I really need to figure out is how to copy and fill down the formula once it's placed in the active cell. Does that explain it any better?
THANKS!
: Ok, so I know this is simple, I just don't kow how to do it ... what I really need to know is how to name the active cell in the copy command when that cell can vary from day to day. Here is an example of the code: : ActiveCell.FormulaR1C1 = "=RC[-1]" : 'THIS NEXT LINE IS WHAT I CAN'T FIGURE OUT ! : Range("ActiveCell:" & "U:" & LastRow).Select : With Selection : .FillDown : .Copy : .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ : False, Transpose:=False : End With : THANKS!
Posted by Tom Urtis on December 06, 2001 5:30 PM
Re: Let me try to restate question
Hmmm, I'm still not sure why the code on my previous post wouldn't apply, as it enters the formula and copies it down as far as any used row exists. It only needs to be modified for column U instead of column B, with the active cell (where the formula is entered) to be adjusted to fit the code you say you have to identify that first used cell in U.
I must be having the same kind of day you are (part of mine was spent in a dentist chair), so if you want to send me your file I can take a look at it and see what I'm missing, and get it back to you by Friday morning.
email: TomUrtis@attbi.com
Tom U.
Sorry Tom, I do seem to be having trouble asking this question properly. What I have is a macro that filters data and column U data can begin on any row each day. I have a subroutine that identifies the last row, and I use a loop to identify the first data cell in column U. What I really need to figure out is how to copy and fill down the formula once it's placed in the active cell. Does that explain it any better? THANKS! : Please correct me if I am misinterpreting your question, but are you essentially interested in copying a formula into a dynamic range, to the last used row? It looks like you know where the formula is supposed to be entered, but maybe the number of rows varies from day to day that must contain a copy of that formula? If so, try this code
Posted by Tom Urtis on December 07, 2001 11:09 AM
Another code approach
This code assumes there is data in column A. It enters the formula in your
first active cell that you've identified in column U, then copies the
formula down as many rows as are populated anywhere in the spreadsheet, then
paste special values the formula over itself in column U.
It also assumes (from what I can tell) that you are trying to copy the
values in column T into column U (ergo the "=RC[-1]").
ActiveCell.FormulaR1C1 = "=RC[-1]"
ActiveCell.Copy
Dim RStart As Range
Dim REnd As Range
Set RStart = ActiveCell.Offset(1, 0)
Set REnd = Sheets("SDD TOTAL").Range("A65536").End(xlUp).Offset(0, 20)
Range(RStart, REnd).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Set RStart = Nothing
Set REnd = Nothing
ActiveCell.Offset(-1, 0).Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Range(ActiveCell, ActiveCell.End(xlDown)).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
HTH
Tom Urtis
Sorry Tom, I do seem to be having trouble asking this question properly. What I have is a macro that filters data and column U data can begin on any row each day. I have a subroutine that identifies the last row, and I use a loop to identify the first data cell in column U. What I really need to figure out is how to copy and fill down the formula once it's placed in the active cell. Does that explain it any better? THANKS! : Please correct me if I am misinterpreting your question, but are you essentially interested in copying a formula into a dynamic range, to the last used row? It looks like you know where the formula is supposed to be entered, but maybe the number of rows varies from day to day that must contain a copy of that formula? If so, try this code
