Defining a Variable Range
An alternative approach.
Turn on the macro recorder
select that range that contains values (or empty)--no formulas, right?
Edit|goto|special
check Constants and ok.
Now just those cells with something in them are selected.
Type the value that's in A1 into the formula bar. But hit ctrl-enter to fill
all those cells.
Turn off the macro recorder.
I got this as my recorded code:
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = "main"
And you could make it nicer:
Columns("B:B").SpecialCells(xlCellTypeConstants, 23).value _
= Range("a1").value
John Baker wrote:
Hi:
I have a simple issue, and iIknow there is a solution. I have copied a cell value, and I
wish to put it in all the cells in a column that have a some value in them (i.e are not
blank). All cells with values are sequential. I have been trying to work with variables,
but somehow come unstuck. The code I have is as follows:
Range("P1").Select
Irow = ActiveCell.Row
Selection.End(xlDown).Select
Irowd = ActiveCell.Row
Range ("a1").select
activecell().copy
'This is where I try and select a range to paste into and it fails.
Range(Irow, Irowd).Select
'unfortunatley that does not work. I
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Help appreciated.
John Baker
--
Dave Peterson
|