inserting a formula in front of a range

[powerdink]

I have a spreadsheet that has data that starts in Column H. I'd like to have my macro insert a formula in front of this column and then copy it all the way down to the last row of data. Here is what I have so far, but I keep getting "application-defined or object-defined error."

Dim rng As Range
 
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("H2").CurrentRegion
 
        With rng.Offset(1, -4).Resize(rng.Rows.Count, 1)
            .Formula = "=some formula in here"
            .Value = .Value     'copy the value and paste it as a value to remove the formula
        End With

I've tried declaring a second range, say starting at cell C2 and going to the row count of rng, but I couldn't get that to work either.

 

[asking]

Try

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("OFFSET($H$2,0,-4,COUNT(OFFSET($H$2,0,0,9999)),1)")
        With rng
            .Formula = "=H2*5%"
            .Value = .Value     'copy the value and paste it as a value to remove the formula
        End With

 

[powerdink]

Thanks,
I actually went back to the declaring two ranges and got this to work.

Dim rng As Range, f As Range 
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("H2").CurrentRegion
Set f = ThisWorkbook.Worksheets("Sheet1").Range("D2").Resize(rng.Rows.Count, 1)
 
With f
            .Formula = "=H2*5%"
            .Value = .Value
End With

Works

 

[asking]

The debug line could not count text,
So new macro ~

Sub ABSOLUTEXCOLUMN()
'
' ABSOLUTEXCOLUMN Macro
' Macro recorded 3/10/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Range("AM4").Select
ActiveCell.FormulaR1C1 = "marked"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
End With
Range("A4:AM4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=">=5 to 6 weeks"
Range("OFFSET($AM$5,0,0,COUNT(OFFSET($A$5,0,0,9999)),1)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormulaR1C1 = "x"
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=25, Criteria1:=">(35) Active"
Range("OFFSET($AM$5,0,0,COUNT(OFFSET($A$5,0,0,9999)),1)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormulaR1C1 = "x"
Selection.AutoFilter Field:=25
Selection.AutoFilter Field:=38, Criteria1:="Yes"
Range("OFFSET($AM$5,0,0,COUNT(OFFSET($A$5,0,0,9999)),1)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormulaR1C1 = "x"
Selection.AutoFilter Field:=38
Selection.AutoFilter Field:=39, Criteria1:="x"
End Sub