+ Reply to Thread
Results 1 to 3 of 3

Reference LastCol VB

Hybrid View

erock24 Reference LastCol VB 02-22-2008, 04:05 PM
shg Maybe like this (untested)? ... 02-22-2008, 04:22 PM
erock24 Well, It turns out that I... 02-22-2008, 07:24 PM
  1. #1
    Forum Contributor
    Join Date
    02-20-2007
    MS-Off Ver
    2003 & 2007
    Posts
    299

    Reference LastCol VB

    I need to edit the following code so the formula does not go into "O"5 and "O"16, but into the last used col 5 and 16, then filldown.

    Dim lastrow1 As Long
    Dim lastrow2 As Long
    
    lastrow1 = Range("B5").End(xlDown).Row
    lastrow2 = Range("B16").End(xlDown).Row
    
        Range("O5").FormulaR1C1 = "=RC[-11]-RC[-10]"
        Range("O5:O" & lastrow1).FillDown
        
        Range("O16").FormulaR1C1 = "=RC[-11]-RC[-10]"
        Range("O16:O" & lastrow2).FillDown
    Any ideas??

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Maybe like this (untested)?
        With Range("O5").End(xlToRight)
            .Resize(lastrow1 - .Row + 1).FormulaR1C1 = "=RC[-11]-RC[-10]"
        End With

  3. #3
    Forum Contributor
    Join Date
    02-20-2007
    MS-Off Ver
    2003 & 2007
    Posts
    299
    Well, It turns out that I have 2 columns to add formulas. I did some research and was able to set my lastcol variables and likewise add the formulas in the correct spots.
    Here is what I have. It seems to work perfect.

    Sheets("FinHL").Activate
    Columns("E:E").Insert shift:=xlToRight
    Range("D3:D200").Copy
    Range("E3").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Dim lastcol1 As Long
    Dim lastcol2 As Long
    Dim lastrow1 As Long
    Dim lastrow2 As Long
    lastcol1 = Cells(5, Columns.Count).End(xlToLeft).Offset(0, -1).Column
    lastcol2 = Cells(5, Columns.Count).End(xlToLeft).Column
    lastrow1 = Range("B5").End(xlDown).Row
    lastrow2 = Range("B16").End(xlDown).Row
    Range(Cells(5, lastcol1), Cells(5, lastcol1)).FormulaR1C1 = "=RC4-RC5"
    Range(Cells(5, lastcol1), Cells(lastrow1, lastcol1)).FillDown
    Range(Cells(16, lastcol1), Cells(16, lastcol1)).FormulaR1C1 = "=RC4-RC5"
    Range(Cells(16, lastcol1), Cells(lastrow2, lastcol1)).FillDown
    Range(Cells(5, lastcol2), Cells(5, lastcol2)).FormulaR1C1 = "=((RC4-RC5)/RC5)"
    Range(Cells(5, lastcol2), Cells(lastrow1, lastcol2)).FillDown
    Range(Cells(16, lastcol2), Cells(16, lastcol2)).FormulaR1C1 = "=((RC4-RC5)/RC5)"
    Range(Cells(16, lastcol2), Cells(lastrow2, lastcol2)).FillDown
    Range(Cells(lastrow2, lastcol1), Cells(lastrow2, lastcol2)).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("A1").Activate

+ Reply to Thread

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