# Multiply ranges

#### Thomas Kuriakose

##### Active Member
Respected Sirs,

Good day to you.

Kindly guide on a vba code to get results on changing currency and the corresponding factor in the attached file.

The attached workbook has multiple tabs in which there are values entered from G3 to Q26. The user will select the currency in B2 of tab A1 and the factor in B3 gets populated based on a matrix of values.

The requirement is to multiple the range G3: K26 and M3:Q26 with the factor in B3 for all tabs.

I found a code on worksheet change event but not bale to use this for this requirement.

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range

Set rInt = Intersect(Target, Range("B3"))
If Not rInt Is Nothing Then
For Each rCell In rInt
If IsNumeric(rCell) Then
With Application
.EnableEvents = False
rCell = rCell * Range("B3")
.EnableEvents = True
End With
End If
Next
End If
End Sub``````
Thank you very much for your kind support and guidance always,

very much appreciated,

with regards,
thomas

#### Attachments

• 26.7 KB Views: 5

#### Marc L

##### Excel Ninja
Hi Thomas !​
According to your attachment as with the data validation it may become a mess via an event procedure​
so better is to use a button on first sheet to launch this Excel Basics beginner starter demonstration :​
Code:
``````Sub Demo1()
Dim Ws As Worksheet
If Not IsNumeric([B3]) Then Exit Sub
[B3].Copy
For Each Ws In Worksheets
Ws.[G3:K26,M3:Q26].PasteSpecial xlPasteValues, xlMultiply
Next
Application.CutCopyMode = False
End Sub``````
Do you like it ? So thanks to click on bottom right Like !​

#### Thomas Kuriakose

##### Active Member
Respected Sir,

You are a live saver, this worked perfectly.

Thank you so much for this insight.

Very much appreciated,

with regards,
thomas

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Apologies for disturbing you again with additional requirement and format change.

As expected, the automation has triggered a new requirement. The original values need to be kept when changing currency and factor.

The cells G3:G8, G10:G15, G17:G22, G24:G29 and copy columns I,K,M and O have the values that need to be multiplied by the factor based on the selected currency and factor respectively.

The new requirement is to keep the original value with each change in currency selection and the multiplication should happen with the original value and the results for each currency and factor can be checked.

We removed the other tabs now due to this changed requirement. I found some codes, but am not sure how to use these in the worksheet change event.

Code:
``````Dim OldValues As New Collection

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Copy old values
Set OldValues = Nothing
Dim c As Range
For Each c In Target
Next c
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Local Error Resume Next  ' To avoid error if the old value of the cell address you're looking for has not been copied
Dim c As Range
For Each c In Target
Debug.Print "New value of " & c.Address & " is " & c.Value & "; old value was " & OldValues(c.Address)
Next c
'Copy old values (in case you made any changes in previous lines of code)
Set OldValues = Nothing
For Each c In Target
Next c
End Sub``````
Is there a way to show the original values every time on a selection.

Kindly guide on this,

Thank you very much for your guidance,

with regards,
thomas

#### Attachments

• 18.8 KB Views: 1

#### Marc L

##### Excel Ninja
As Excel is very not a database software you should use a specific range / sheet to store the previous values before to apply any factor …​

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Thank you very much for this guidance.

Sir, correct me if I am wrong, the pervious values need to be copied to a specific range and then apply the factor and when we change the selection of currency we need to again copy the original values from the specific range to the related cells before applying the factor with every change.

If you have time, kindly guide on a code to carry out this event. We can copy this to the Data tab.

Thank you very much once again,

with regards,
thomas

#### Attachments

• 20.2 KB Views: 1

#### Thomas Kuriakose

##### Active Member
Respected Sir,

I found two sets of code for save previous cell value to another column cell and sheet store.

My knowledge in vba is too raw to get this applied for our requirement.

Kindly guide on this,

Thank you very much,

with regards,
thomas

#### Attachments

• 2 KB Views: 0
• 1.2 KB Views: 0

#### Marc L

##### Excel Ninja
If you have time, kindly guide on a code to carry out this event.
Not too much time … As it depends on the expected result so elaborate or well describe your need,​
maybe attach the 'after' workbook according to your post #6 'before' workbook …​
Rather than wasting time 'to find a code' start from activating the Macro Recorder and operate manually :​
you will get easily & quickly your own code base, as a good start to learn Excel / VBA basics …​

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Thank you very much for your support on this query.

I recorded two macros actioned by calculate and reset button, and attached is the file for your kind reference.

1. Calculate : This copies the initial values from A1 tab G,I,K,M&O to data tab B:F and then multiplies the factor in A1 tab B3 with data tab B:F values and gives the result in H:L in data tab.
2. Reset: This copies the reset stored values in data tab O:S back to A1 tab columns G,I,K,M&O.

Kindly help with the following -

a) Shorten this recorded macros with a sequence where user cannot make error.
b) The reset stored values in data tab O:S are manually copied from A1 tab G,I,K,M&O, how can this be automated, when I run macro and store it here it copies the last values in A1 tab.

Code:
``````Sub Calculate()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("G:G,I:I,K:K,M:M,O:O").Select
Range("O1").Activate
Selection.Copy
Sheets("Data").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Sheets("A1").Select
Range("G1").Select
Application.CutCopyMode = False
Sheets("Data").Select
Range("B2:F2").Select
Selection.Copy
Range("H2").Select
ActiveSheet.Paste
Range("H3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-6]*'A1'!RC[-6]"
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H8"), Type:=xlFillDefault
Range("H3:H8").Select
Range("H3").Select
ActiveCell.FormulaR1C1 = "=RC[-6]*'A1'!R3C2"
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H8"), Type:=xlFillDefault
Range("H3:H8").Select
Selection.Copy
Range("H10").Select
ActiveSheet.Paste
Range("H17").Select
ActiveSheet.Paste
Range("H24").Select
ActiveSheet.Paste
Range("I3").Select
ActiveSheet.Paste
Range("I10").Select
ActiveSheet.Paste
Range("I17").Select
ActiveSheet.Paste
Range("I24").Select
ActiveSheet.Paste
Range("J3").Select
ActiveSheet.Paste
Range("K3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveSheet.Paste
Range("J10").Select
ActiveSheet.Paste
Range("K10").Select
ActiveSheet.Paste
Range("L10").Select
ActiveSheet.Paste
Range("J17").Select
ActiveSheet.Paste
Range("K17").Select
ActiveSheet.Paste
Range("L17").Select
ActiveSheet.Paste
Range("J24").Select
ActiveSheet.Paste
Range("K24").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("J24:J29").Select
Selection.Copy
Range("K24").Select
ActiveSheet.Paste
Range("L24").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("H2:L2").Select
Selection.Copy
Range("H9").Select
ActiveSheet.Paste
Range("H16").Select
ActiveSheet.Paste
Range("H23").Select
ActiveSheet.Paste
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Range("G2").Select
Sheets("A1").Select
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Columns("K:K").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Columns("M:M").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Columns("O:O").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Sheets("Data").Select
Range("H2").Select
Sheets("A1").Select
Range("G2").Select
End Sub``````
Code:
``````Sub Reset()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+b
'
Sheets("Data").Select
Columns("O:O").Select
Selection.Copy
Sheets("A1").Select
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("P:P").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Columns("K:K").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Range("M1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Columns("S:S").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A1").Select
Range("O1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
End Sub``````
Thank you very much for your kind support,

with regards,
thomas

#### Attachments

• 30.3 KB Views: 6

#### Marc L

##### Excel Ninja
According to your last attachment it may be easy as​
Code:
``````Sub Calculate()
Dim A\$, C&
If Not IsNumeric([B3]) Then Exit Sub
Application.ScreenUpdating = False
Sheet2.UsedRange.Clear
With [F2].CurrentRegion.Columns
.Copy Sheet2.[F2]
[B3].Copy
For C = 2 To .Count Step 2:  .Item(C).PasteSpecial xlPasteValues, xlMultiply:  Next
End With
Range(A).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub``````
and​
Code:
``````Sub Reset()
Sheet2.[F2].CurrentRegion.Copy [F2]
End Sub``````
You may Like it !

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Amazing, this works perfectly. Thank you so very much.

Just one question, correct me if I am wrong, so for the original data in A1 from F2 , should the copy in data tab be identically copied to F2 or the position of the copy can change be in data tab at any cell.

Thank you very much once again.

with regards,
thomas

#### Marc L

##### Excel Ninja
The Sheet2 'data' copy destination can be A1, whatever the cell … I kept the same address just for a visual confort.​

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Thank you so much for this confirmation.

Sir, apologies for asking for more, I input the data in the actual file, the region copies all the data in A1 tab the sheet to data tab. The data tab has data validation inputs for the tab A1. This also clears all value in data tab.

how to tweak this code to only use values in A1 tab G,I,K,M&O since there are calculations after these data inputs.

I attached the file with the change in A1 tab and also the other input values for data validation in data tab.

Thank you very much for your kind support,

with regards,
thomas

#### Attachments

• 31.1 KB Views: 1

#### Marc L

##### Excel Ninja
I was thinking 'Data' sheet does not need all this stuff as it was just for values storage​
and I do not understand why initial values & reset values as both ranges are the same so one seems superfluous …​
So do you really need 'Data' sheet as it is ?​
If so, you must first remove the WorkSheet_Change event from Sheet1 (A1)​
then you could try​
Code:
``````Sub Calculate()
If Not IsNumeric([B3]) Then Exit Sub
Application.ScreenUpdating = False
[B3].Copy
For C% = 0 To 4
With [G2:G29].Offset(, C * 2)
Sheet2.[B2:B29].Offset(, C).Value2 = .Value2
Sheet2.[O2:O29].Offset(, C).Value2 = .Value2
.PasteSpecial xlPasteValues, xlMultiply
Sheet2.[H2:H29].Offset(, C).Value2 = .Value2
End With
Next
Application.CutCopyMode = False
Range(A).Select
Application.ScreenUpdating = True
End Sub``````
and​
Code:
``````Sub Reset()
For C% = 0 To 4:  [G2:G29].Offset(, C * 2).Value2 = Sheet2.[O2:O29].Offset(, C).Value2:  Next
End Sub``````
You should Like it !​

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Thanks so much for this guidance and support.

Sorry for all the changes, but I thought this would work in the actual file.

We had prepared the data sheet to have all the inputs for the final sheet, The final sheet has a lot of data in the actual file. and hence we used the same sheet to store the values. Kindly suggest a better way if this is not correct.

I was not able to use the initial values as it kept changing after multiplication from the recorded macro. Then I manually copied the initial values to reset value without a macro since I was not able to get a copy macro for values storage without changing values.

Sir, also is there a way to prevent user from clicking of calculate button before reset. When I tested today, if we had pressed the calculate button without resetting the knew values were getting multiplied.

Thank you very much for your support once again,

with regards,
thomas

#### Marc L

##### Excel Ninja
I just edited my previous post (as I did not see your last one) for some tweak procedures according to your last attachment …​

#### Marc L

##### Excel Ninja
is there a way to prevent user from clicking of calculate button before reset. When I tested today, if we had pressed the calculate button without resetting the knew values were getting multiplied.
So just use an unique button with an unique VBA procedure !​
Once 'Calculate' is done, the button name changes to 'Reset' and vice versa,​
the VBA procedure just has to test the button name …​

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Thanks so much, this worked now perfectly, only one question will I have to copy manually the original values in range O2:O29 every time or this will be copied from A1 tab. I could not understand this part.

I also added the unique button and it works as you suggested very correctly.

Kindly find attached the final file for your reference,

Thanks so much once again,

with regards,
thomas

#### Attachments

• 27.7 KB Views: 4

#### Marc L

##### Excel Ninja
will I have to copy manually the original values in range O2:O29 every time
No as the original values are yet copied to `Sheet2.[O2:O29]` like you must see within the code …​

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Thank you so much for the great support on this query.

Very much appreciated,

with regards,
thomas