CODEWITHSUNDEEP
vbaexcel
toucansame
toucansame

Reputation: 164

Use Cell Attribute in R1C1 Notation

I'm running a macro where I define lastcolumnbc using the column attribute

lastcolumnbc = ws.Cells.SpecialCells(xlCellTypeLastCell).Column

Later, I would like to use that attribute in R1C1 to insert a formula into a range:

Set rgbe = .Range(.Cells(1, 2), .Cells(lastrow - 1, 2))
rgbe.Select
Selection.FormulaR1C1 = "=INDEX(RC[1]:RC[lastcolumnbc],MATCH(TRUE,INDEX((RC[1]:RC[lastcolumnbc]<>0),0),0))"
Selection.Columns.AutoFit

Unfortunately this returns an object-defined error.

Upvotes: 0

Views: 565

Answers (3)

QHarr
QHarr

Reputation: 84465

Without knowing which line throws the error I will assume for now lastrow can be determined in the same way as last column. And that your final error reside with not concatenating the variable outside the string.

Always use option explicit at the top of your code, avoid select and use With statements to be clear on sheet working with. And be careful as xlCellTypeLastCell may actually end up referring to a cell that you didn't expect and may even appear empty.

Option Explicit

Sub test()
    Dim lastcolumnbc As Long
    Dim ws As Worksheet
    Dim rgbe As Range
    Set ws = ActiveSheet
    Dim lastrow As Long
    lastrow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
    lastcolumnbc = ws.Cells.SpecialCells(xlCellTypeLastCell).Column

    With ws
         If lastRow <=1 Then Exit Sub 'quit if attempt invalid 
        Set rgbe = .Range(.Cells(1, 2), .Cells(lastrow - 1, 2)) 'what happens if lastRow is 1 or 0?
        With rgbe
           .FormulaR1C1 = "=INDEX(RC[1]:RC[" & lastcolumnbc & "],MATCH(TRUE,INDEX((RC[1]:RC[" & lastcolumnbc & "]<>0),0),0))"
          .Columns.AutoFit
       End With

    End With

End Sub

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Does this work for you?

rgbe.FormulaR1C1 = "=INDEX(RC[1]:RC[" & lastcolumnbc & "],MATCH(TRUE,INDEX((RC[1]:RC[" & lastcolumnbc & "]<>0),0),0))"

Upvotes: 0

Raunak Thomas
Raunak Thomas

Reputation: 1403

The value of the variable will need to be put in the string for the formulae to work. Like so

Selection.FormulaR1C1 = "=INDEX(RC[1]:RC[lastcolumnbc],MATCH(TRUE,INDEX((RC[1]:RC["& lastcolumnbc & "]<>0),0),0))"

Upvotes: 0

Related Questions