?
Solved

Excel VBA modification

Posted on 2013-11-15
2
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

762 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