MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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