CODEWITHSUNDEEP
excelvba
MaxF
MaxF

Reputation: 55

Converting VBA formula into R1C1 format

Please help convert the formula below into R1C1 format:

ActiveSheet.PageSetup.PrintArea = "$A$1:$L$49"

Thank you!

Upvotes: 0

Views: 517

Answers (1)

Gove
Gove

Reputation: 1804

If you have the access to the row and column numbers, I recommend never going to full R1C1 notation as follows:

Sub test()
    ActiveSheet.PageSetup.PrintArea = rc2a1(1, 1, 49, 12)
End Sub



Function rc2a1(start_row As Long, start_col As Integer, Optional end_row As Long, Optional end_col As Integer)
    Dim address As String
    address = ThisWorkbook.Worksheets(1).Cells(start_row, start_col).address
    
    If end_row > 0 And end_col > 0 Then
      address = address & ":" & ThisWorkbook.Worksheets(1).Cells(end_row, end_col).address
    End If
    
    rc2a1 = address
End Function

If you have the absolute R1C1 notation, then this the approach:

Sub test()
    ActiveSheet.PageSetup.PrintArea = rc2a1("r1c1:r49c12")
End Sub


Function rc2a1(reference As String)
    Dim x As Byte
    Dim refs As Variant
    Dim parts As Variant
    refs = Split(reference, ":")
    
    For x = 0 To UBound(refs)
      parts = Split(UCase(refs(x)), "C")
      parts(0) = Mid(parts(0), 2)
      refs(x) = ThisWorkbook.Worksheets(1).Cells(CLng(parts(0)), CLng(parts(1))).address
    Next
    
    rc2a1 = Join(refs, ":")
End Function

Upvotes: 1

Related Questions