[SOLVED] Defining a Variable Range

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

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

Dave:

Thanks very much. The use of the GOTO/Special is something that I had not explored. It
sounds as if it could be very useful in some settings.

Best

John

Dave Peterson <ec35720@netscapeXSPAM.com> wrote:

>
> Columns("B:B").Select
> Selection.SpecialCells(xlCellTypeConstants, 23).Select
> Selection.FormulaR1C1 = "main"