• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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
        OldValues.Add c.Value, c.Address
    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
        OldValues.Add c.Value, c.Address
    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

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

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

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
         A = ActiveCell.Address
         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
 

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

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
        A$ = ActiveCell.Address
        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

Top