Results 1 to 5 of 5

dynamic column reference, copy formula down

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-29-2011
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    122

    dynamic column reference, copy formula down

    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
    Attached Files Attached Files
    Last edited by intothewild; 12-03-2012 at 06:28 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1