Solved

Excel VBA modification

Posted on 2013-11-15
2
233 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now