Automatically multiply certain range with a number.

[sarita]

I Have a spreadsheet with data raging from C17 to F90. I want to write a macro which could take the data on from each cell one by one and multiply it with 2 differnt numers.
EX:
First it should pick the data in all the colums and rows and multiply it with 2 and paste it on new sheet (Sheet 2) and then it again should get the data on sheet 1 and multiply with 5 and paste on Sheet 3.

Thanks,
Sarita

[mudraker]

sarita

Why not just use formulas

in sheet2 cell c17 you would need a fourmula like =Sheet1!C17


If you really need a macro then try this one


Sub Multiplier()
Dim Rng As Range
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet

Set Ws1 = Worksheets("Sheet1")
Set Ws2 = Worksheets("Sheet2")
Set Ws3 = Worksheets("Sheet3")

For Each Rng In Ws1.Range("c17:f90")
If IsNumeric(Rng.Value) Then
Debug.Print Rng.Address
Ws2.Range(Rng.Address).Value = Rng.Value * 2
Ws3.Range(Rng.Address).Value = Rng.Value * 5
End If
Next Rng
End Sub

[sarita]

Thanks mudraker !

It really helped.
The reason why i wanted it in codes rather than formulas because I wanted to add this functionality to my pre existing code.

Thanks,
Sarita