RefEdit Userform Math

[RunRabbit13]

Trying to use a userform to allow user to select ranges of data and quickly calculate the percent difference. I have been working on this for hours with no positive outcome. I can get it to work when you select one cell (not the code I pasted), but I cannot get ranges to work. If someone know how to make this happen please let me know what I am doing wrong.

Private Sub CommandButton1_Click()
Dim Addr As String
Dim Rng1, Rng2 As Range
Dim Location As Variant


'New Value
Addr1 = RefEdit1.Value
Set Rng1 = Range(Addr1)


'Old value
Addr2 = RefEdit2.Value
Set Rng2 = Range(Addr2)


'Output
Addr3 = RefEdit3.Value
Set Location = Range(Addr3)




Location = (Rng1 - Rng2) / Rng2




End Sub

 

[shg]

How about just ...

Private Sub CommandButton1_Click()
  Dim r1            As Range
  Dim r2            As Range
  Dim r3            As Range

  On Error GoTo Nevermind
  Set r1 = Application.InputBox("Range 1?", Type:=8)
  Set r2 = Application.InputBox("Range 2?", Type:=8)
  Set r3 = Application.InputBox("Range 3?", Type:=8)

  r3.Value = r1(1).Value / r2(1).Value
  Exit Sub

Nevermind:
  Beep
End Sub

 

[RunRabbit13]

Hi shg,

I originally was using input boxes. The issue I ran into there was not being able to do calculations for the range. I appreciate the code but when run it duplicates the first calculation. Please see example below:

Output from code:
Column A | Column B | Column C
8 9 .88889
8 10 .88889
1 10 .88889

What I am hoping for:

Column A | Column B | Column C
8 9 .13
8 10 .25
1 10 9.00

 

[shg]

... but when run it duplicates the first calculation.

Duplicates what first calculation?

 

[RunRabbit13]

Duplicates what first calculation?

The code using the input boxes only works on the first cells in the ranges. Rng3 duplicates this answer for all other cells in Rng3 (see image). This is why I thought maybe RefEdit would work so I could calculate it for varying ranges. I am still not sure if RefEdit is the way to go with that or if you can do it with input boxes.

 

[shg]

Maybe it's time for you to explain what you're trying to do.

 

[RunRabbit13]

Maybe it's time for you to explain what you're trying to do.

I am hoping a picture will help show what I am trying to accomplish. In Column A is data I want to compare to Column B to compute the percent variance. I am hoping for the outcome to appear in Column C (or wherever the user selects for it to go). The code above will only calculate what is in Row 1 and duplicate that for all other rows. Where I have been stuck is getting VBA to allow me to get a different answer for each new row of data. Sometimes there may only be six number to compare, other times there may be 165. This is why I am trying to build a macro using ranges to quickly calculate the percent variance.

 

[shg]

Doing what you're trying to do seems way more difficult and slower than entering =1-B1/A1 in C1 and double-clicking the fill handle.

 

[RunRabbit13]

Doing what you're trying to do seems way more difficult and slower than entering =1-B1/A1 in C1 and double-clicking the fill handle.

It is part of a toolbar. Thanks for trying to help