Cell Range VBA

[Carl Clements]

Hi,

Can anybody help me with some code that will select a range of cells within a row, where the row number or ActiveCell will not always be the same.

In detail I have formulas in columns C:G and I need to select them and copy them down a number of rows, already pre-determined by a variable (CountLODH).

I have the following code that selects the formulas, but then fails on the second line with a Run-time error '1004': Method 'Range' of object'_Global failed

Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, CountLODH).Select

Thanks,
Carl

 

[VoG]

Perhaps

Selection.Resize(CountLODH).Select

but selecting is best avoided.

 

[Misca]

Maybe something like:

Sub FillFormula()
Dim CountLODH As Long   'Last row to fill the formula to
Dim FirstRow As Long    'First row to fill the formula
CountLODH = ActiveCell.Row  'Uses the ActiveCell row
FirstRow = Range("C" & Rows.Count).End(xlUp).Row + 1    'Uses first empty row in column C
If FirstRow > CountLODH Then Exit Sub   'If activecell.row < first empty row then exit sub
Range("C" & FirstRow & ":G" & CountLODH).Formula = Range("C2:G2").Formula 'Fills the range with the formula from C2:G2
End Sub

 

[Carl Clements]

Many Thanks. The Resize code worked.