Applying a multiplication by macro on a column

[Marty]

Hi. Need some help.

I have attached a copy of my excel for my macro.

In a range from column A until end of his selection, i would like to apply to column B a Multiplication formula.

In a sample way, i would like to do anything available in column A to be multiplied by 12 in column B.

Any sample way of doing it ?

any help is appreciated.

thanks
Marty

[SamT]

Sorry. I can't open Office >= 7 files on this computer

I'll leave you to work out the details

Sub SamT()
Range("B:B") = 12
Range("A:A").Copy
Range("B1").PasteSpecial XlPasteType:=pasteall, XlPasteSpecialOperation:=xlPasteSpecialOperationMultiply
End Sub

[jolivanes]

Or

Sub Multiply_By_Twelve_A()
With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
.Formula = "=RC[-1]*12"
.Value = .Value
End With
End Sub

[jolivanes]

Or this might work also.

Sub Multiply_By_Twelve_B()
Dim rng As Range
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
rng.Offset(, 1) = Evaluate(rng.Address & "*12")
End Sub

[snb]

or


Sub M_snb()
[B2:B200] = [if(A2:A200="","",A2:A200*12)]
End Sub

[Marty]

Hi

All your help is much appreciated, it is working to the perfection. :thumb

thanks guys!

[jolivanes]

Hoi snb,
Now that we have you on the line, is it possible to make your solution a variable solution?
I have seen these solutions before but they're always hard coded like in your solution the 200.
Thanks

@ Marty
You should have enough possibilities now
Good luck

[Marty]

Hi, indeed i have enough possibilities and now all is working to the perfection
thanks again.

[snb]

Hoi snb,
Now that we have you on the line, is it possible to make your solution a variable solution?
I have seen these solutions before but they're always hard coded like in your solution the 200.
Thanks



This approach is variable; you can see it in the 'if' condition.
Can you specify what should be the 'variable' ?

You can easily adapt the code to e.g.


Sub M_snb()
[B2:B200000] = [if(A2:A200000="","",A2:A200000*12)]
End Sub

[jolivanes]

OK, thank you very much.
I'll have to play with it to fully see the results.
What I was thinking is what is the norm in a variable range like

Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Instead of the

A2:A200000
Before I put my foot in my mouth any deeper, I'll try it on some different scenarios.
Thanks again

[Aussiebear]

Actually, I was thinking more like yours jolivanes. Given that sub's approach is predetermined by guessing what the range could be ("A200" or "A20000")

[jolivanes]

OK, I am satisfied!
On a 4000 cell range, there is a noticeable speed difference. The solution from snb in post #9 being the slower one. (If my memory serves me right, this must be the first time my solution "outdoes" snb's)
Geen kwade bedoeling hier snb (No bad intentions here snb)
After using snb's code and going "End" + "Home", you'll end up at row 200,000 what's understandable of course.
So, like Aussiebear more or less suggested, I'll stick with what I know for now.
Thank both you gentlemen for your lessons

[snb]

Sub M_snb()
cells(1).currentregion.columns(1).name="snb"
[snb].offset(,1) = [if(snb="","",snb*12)]
End Sub

But if you are certain column A only contains numericals:


Sub M_snb()
Cells(1).CurrentRegion.Columns(1).Name = "snb"
[snb].Offset(, 1) = [snb*12]
End Sub

Is this 'variable' enough ? (zei hij ironisch ;) )

[jolivanes]

What do you call this? Outside the box thinking (or something similar)
I like it.
Thanks again
John

[SamT]

Sub Try_This()
LR = Cells(Rows.Count, "A").End(xlUp).Row: A = "A2:A" & LR: B = "B2:B" & LR
[B] = [if(A="","",A*12)]
End Sub


If you're certain
Sub Try_This()
LR = Cells(Rows.Count, "A").End(xlUp).Row: A = "A2:A" & LR: B = "B2:B" & LR
[B] = [A*12]
End Sub

[snb]

@Sam

You don't need any variables in this case:


Sub M_snb()
With Cells(1).CurrentRegion.Columns(1)
.Offset(, 1) = Evaluate("if(" & .Address & "="""",""""," & .Address & "*12)")
End With
End Sub

NB You can't use a variable in evaluate in the [ ... ] bracket format.

[SamT]

You can't use a variable in evaluate in the [ ... ] bracket format.

Ahah.

[snb]

@SamT

If you are addicted to variables in combination with 'evaluate':


Sub M_snb()
c00= Cells(1).CurrentRegion.Columns(1).address

Cells(1).CurrentRegion.Columns(1).Offset(, 1) = Evaluate("if(" & c00 & "="""",""""," & c00 & "*12)")
End Sub

[SamT]

I have yet to advance to using Evaluate. I may never so advance, since this is only a hobby for me.

I still want to learn as much as I can about as many fields of endeavor as I can.

One of these days I may get serious about Physics, Astrophysics, and Quantum Physics and learn how to disprove my very own Theory of Gravity.

Or maybe, I will move to California and teach my Granddaughters how to not be socialists. :D

I am retarded retired and can do as I wish.