Results 1 to 5 of 5

Convert A1 formulas to R1C1 Formula Array

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-27-2012
    Location
    New Brunswick, Canada
    MS-Off Ver
    Excel 2016
    Posts
    349

    Convert A1 formulas to R1C1 Formula Array

    I was given the following code and am trying to understand and modify it myself in VBA. I understand it has to be written in R1C1 as opposed to A1 format and I am probably just making a typo I haven't noticed.
    A4 - BLANK CELL
    B4 - BLANK CELL
    C4 - =IF(A4=0,"0",(A4+B4/60))
    D4 - BLANK CELL
    E4 - BLANK CELL
    F4 - =IF(D4=0,"0",(D4+E4/60))
    G4 - =IF(A4=0,"0",(F4/C4))
    H4 - =TEXT(I4,"dddd")
    I4 - Column I is date formatted and right now the cell value is 6/1/2013
    J4 - =IF(A4=0,"",(F4)/(C4))
    K4 - BLANK CELl
    L4 - BLANK CELL
    M4 - =IF(K4=0,"0",(K4+L4/60))
    N4 - =IF(A4=0,"",(M4)/(C4))

    Then at the bottom I have a another row of formulas (most of which sum the columns)
    A35 to F35 - =SUM(A4:A35). The column reference of course changes from A to F
    G35 - =IF(A35=0,"0",(F35/C35))
    H35 - BLANK CELL
    I35 - BLANK CELL
    J35 - =IF(A35=0,"",(F35)/(C35))
    K35 to M35 - =SUM(K4:K35). The column reference of course changes from K to M
    N35 - =IF(A35=0,"",(M35)/(C35))

    Attached are the codes I was given but I need to change it to incorporate these columns/formulas instead.
    .Range(.Cells(4, 1), .Cells(4, 14)).Formula = Array(vbNullString, vbNullString, "=IF(RC[-2]=0,""0"",(RC[-2]+RC[-1]/60))", vbNullString, vbNullString, _
                    "=IF((RC[-2]+RC[-1])=0,""0"",(RC[-2]+RC[-1]/60))", "=IF(RC[-6]=0,""0"",(RC[-1]/RC[-4]))", vbNullString, vbNullString, _
                    "=IF((RC[-2]+RC[-1])=0,""0"",(RC[-2]+RC[-1]/60))", "=IF((RC[-3]+RC[-2])=0,""0"",(RC[-1]/RC[-8]))", vbNullString, vbNullString, _
                    "=IF(RC[-2]=0,""0"",(RC[-2]+RC[-1]/60))", vbNullString, vbNullString, _
                    "=IF((RC[-2]+RC[-1])=0,""0"",(RC[-2]+RC[-1]/60))", "=IF(RC[-6]=0,""0"",(RC[-1]/RC[-4]))", vbNullString, vbNullString, _
                    "=IF((RC[-2]+RC[-1])=0,""0"",(RC[-2]+RC[-1]/60))", "=IF((RC[-3]+RC[-2])=0,""0"",(RC[-1]/RC[-8]))", _
                    "=TEXT(RC[1],""dddd"")", vbNullString, vbNullString, "=SUM(RC[-18]+RC[-7])", _
                    "=SUM(RC[-18]+RC[-7])", "=IF(RC[-2]=0,""0"",(RC[-2]+RC[-1]/60))", "=IF(RC[-28]=0,"""",(RC[-1])/(RC[-26]+RC[-15]))")
    .Range(.Cells(bytDays + 4, 1), .Cells(bytDays + 4, 14)).Formula = Array("=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", _
                    "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=IF(RC[-6]=0,""0"",(RC[-1]/RC[-4]))", "=SUM(R[-30]C:R[-1]C)", _
                    "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=IF((RC[-3]+RC[-2])=0,""0"",(RC[-1]/RC[-8]))", _
                    "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", _
                    "=IF(RC[-6]=0,""0"",(RC[-1]/RC[-4]))", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=IF((RC[-2]+RC[-1])=0,""0"",(RC[-2]+RC[-1]/60))", _
                    "=IF((RC[-3]+RC[-2])=0,""0"",(RC[-1]/RC[-8]))", vbNullString, vbNullString, "=IF(RC[-24]=0,"""",(RC[-19]+RC[-8])/(RC[-22]+RC[-11]))", _
                    "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=SUM(R[-30]C:R[-1]C)", "=IF(RC[-28]=0,"""",(RC[-1])/(RC[-26]+RC[-15]))")
    Is there an easy way to write a formula array aside from keying letter by letter? That is my problem, I'm sure my syntax is just all wrong. I've inserted a copy of the workbook as well.
    Attached Files Attached Files

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