Convert A1 formulas to R1C1 Formula Array

**Wheelie686** · 06-27-2013, 07:09 PM

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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.

**alansidman** · 06-27-2013, 07:54 PM

If you write the formulas in A1 format in a cell. Then select option from the MS Button in the upper left corner. Select formulas, check the box for R1C1. Close the options. The formula that you created in A1 will now be in R1C1 so you can verify the veracity and copy it to your VBA code. This will help you to learn R1C1.

Alan

**Wheelie686** · 06-27-2013, 10:46 PM

wow I totally didn't know that thanks! Still issues with writing the Array formula though, which I'm sure is just a punctuation or spelling mistake...

**TMS** · 06-28-2013, 07:30 AM

The other alternative is to create a working formula in a cell.

Then:

select the cell
start the macro recorder
press F2 to go into Edit Mode
press Enter, or Ctrl-Shift-Enter for an Array Formula, to recommit the formula
stop the macro recorder.

look at the code in the VBE editor

For example:

Please Login or Register  to view this content.

**TMS** · 06-28-2013, 07:38 AM

Sorry, I meant to point out that the complication with R1C1 notation is that the formula is relative to the cell that contains the formula.

Regards, TMS