Solved

Excel VBA modification

Posted on 2013-11-15
2
238 Views
Last Modified: 2013-11-15
I recently changed the Position of my amount originally in J2 and all subsequent ones to the H2 Cell... I have the following code that adds a formula storting in J4 that does H4/J$2 but now that I moved this I need the formula to look at H4/H$4 .... How can I modify this code to accomplish this?  

Dim vRow As Long
 vRow = Cells(Rows.Count, 10).End(xlUp).Row
 Intersect(Rows("4:" & vRow), Range("J:J,N:N,R:R,V:V,Z:Z,AD:AD,AH:AH,AL:AL,AP:AP,AT:AT,AX:AX,BB:BB")).FormulaR1C1 = "=RC[-2]/R2C"

Open in new window

Hours-Input-Ver-3.1-Revised.xlsm
0
Comment
Question by:joeserrone
2 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 39651783
Hi Joe,

The part of that which has the formula is "=RC[-2]/R2C"
That uses "r1c1" notation, which refers to rows and columns (both absolute references, and relative to where the cell is). The "RC[-2]" means "same row, 2 columns to the left" which is what references column H from J.
"R2C" means "row 2, same column" which is what is J2. You should change it to R2C[-2] which would be H$2 (or "same column as the numerator, but row 2 of that column instead"

Matt

EDIT: if you always want the demoninator to be $H$2, then change R2C to R2C8. Doesn't sound like that's exactly what you want though.
0
 

Author Closing Comment

by:joeserrone
ID: 39651954
Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question