Ive got this macro rounding numbers and copying the formula down but not sure how to make a dynamic column reference.
In the code ive put the header Price2 in column I specifically, this wouldnt be the case normally, in reality id have to search for the header "Price2" which probably
wont be in 'column I' ..how would i make the references to the "Price2" header dynamic?
Sub roundFigs()
Workbooks("main12.xls").Worksheets("Sheet1").Activate
Dim pt2nom
Dim endRow
Dim rPrCol
'enter Price2 header
Cells(1, "I").Value = "Price2"
' get last row and column
pt2nom = Sheets("Sheet1").Rows(1).Find("Price", LookAt:=xlWhole).Column
endRow = Sheets("Sheet1").Cells(Rows.Count, pt2nom).End(xlUp).Row
rPrCol = Sheets("Sheet1").Rows(1).Find("Price2", LookAt:=xlWhole).Column
'''''''''''''''''''''''''''''''''''''''''''
' Need to make this next part dynamic '
'need to put data in any column not just I'
'''''''''''''''''''''''''''''''''''''''''''
Sheets("Sheet1").Range("I2").FormulaR1C1 = "=ROUND(RC[-6],2)"
Range("I2").Select
Selection.Copy
Range("I3:I" & endRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("I1").Select
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("I:I").EntireColumn.AutoFit
Application.CutCopyMode = False
Range("I1").Select
End Sub
Bookmarks