Solved

Excel VBA modification

Posted on 2013-11-15
2
228 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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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…

763 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

6 Experts available now in Live!

Get 1:1 Help Now