Excel VBA: Add multiplier to column without iterations or modifying outside column.

Is there a one step VBA method for adding a multiplier to all numbers in range("C2:C100") WITHOUT iterations or modifying any cell outside range("C2:C100")

Example: Multiply all numbers in column C by 20.
*I have considered: sheets("Sheet2").range("C2:C100").formula = "=C2*20" nonetheless, the formula copies over the values before they are calculated.
ouestqueAsked:
Who is Participating?
 
Rgonzo1971Commented:
of course if you activesheet is not Sheet2 then try
Sheets("Sheet2").Range("C2:C100").Value = Evaluate("=if(ISNUMBER(Sheet2!C2:C100),Sheet2!C2:C100*20,IF(Sheet2!C2:C100<>"""",Sheet2!c2:c100,""""))")

Open in new window

0
 
Martin LissOlder than dirtCommented:
Did you mean this to be about Excel rather than Access?
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
my question too, Martin!

yes, in Excel, you can adjust all numbers in a column like this:

1. copy what you want to add, subtract, multiply, or divide by
2. select range to change
3. Paste Special -- multiply
4. OK

original:
1
2
3
4
5
6
7
8
9
10

changed:
20
40
60
80
100
120
140
160
180
400

have an awesome day,
crystal
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ShumsDistinguished Expert - 2017Commented:
If its excel you may try below code:
Sub AddMultiplier()
Dim Ws As Worksheet
Dim Rng As Range, c As Range
Set Ws = Worksheets("Sheet2")
Set Rng = Ws.Range("C2:C100")
Application.ScreenUpdating = False
For Each c In Rng
    c.Value = c.Value * 20
Next c
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
here is a screenshot for the example I gave above:
Excel - Paste Special - Multiply all numbershave an awesome day,
crystal
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
With VBA you cannot do that with just one line
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
To do this with VBA, you can iterate* through a selection. Then you don't have to know the sheet or range -- perhaps use InputBox to ask what number and operation.  Please explain more about what you are doing.  

* no way to get around iterating because the formula/value is different each time
0
 
Rgonzo1971Commented:
Hi,

pls try
Sheets("Sheet2").Range("C2:C100").Value = Evaluate("=IF((C2:C100)*20,C2:C100*20,"""")")

Open in new window

Regards
2
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
cool, didn't know you could do that!
0
 
Rgonzo1971Commented:
if you have also text in your range then try
Sheets("Sheet2").Range("C2:C100").Value = Evaluate("=if(ISNUMBER(C2:C100),C2:C100*20,IF(C2:C100<>"""",c2:c100,""""))")

Open in new window

0
 
ShumsDistinguished Expert - 2017Commented:
If your concern is to show formula in specified range instead of values, you may try below:
Sub AddMultiplier()
Dim Ws As Worksheet
Dim Rng As Range
Set Ws = Worksheets("Sheet2")
Set Rng = Ws.Range("C2:C100")
Application.ScreenUpdating = False
For Each c In Rng
    c.Formula = "=" & c & "*20"
Next c
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
ShumsDistinguished Expert - 2017Commented:
Rgonzo Sir,

I believe OP mentioned
Example: Multiply all numbers in column C by 20.

Its not just C2 value to be multiplied in all the cells in specific range.
0
 
Rgonzo1971Commented:
@Shums it multiplies every cell of C2:C100 (array formula)
0
 
ouestqueAuthor Commented:
Wow!!! That is amazing. Very Very Cool!!! Thank you so much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.