CODEWITHSUNDEEP
excelvba
Daniel05
Daniel05

Reputation: 105

Visual Basic Excel Macro

I want to have a little Macro in excel. The Macro should take a double number from A1 and multiply this number by 5 and return it back into A1 but i always get error messages etc.

My code up to now:

Function multiply()
Dim a As Long
For a = 1 To 65563
cell(a,3) = cell(a,3).Value * 5
next a
End Function

I have not worked with VBA before.

Upvotes: 0

Views: 1168

Answers (4)

Sam
Sam

Reputation: 7303

That does what you ask, but it's a sub as you can't edit worksheet values using a function

 Public Sub multiply()
    On Error GoTo err
    Dim val As Long
    val = ActiveSheet.Cells(1, 1).Value
    ActiveSheet.Cells(1, 1).Value = val * 5
    Exit Sub
 err:
 End Sub

Upvotes: 3

brettdj
brettdj

Reputation: 55672

You don't actually need a macro for this - although if you do want to use VBA you can avoid time consuming loops altogether

1 Manual Method using Paste Special - Multiply

from walkenbach

To increase a range of values by 5 times:

  1. Enter 5 into any blank cell.
  2. Select the cell and choose Edit, Copy.
  3. Select the range of values (Column A in your example) and choose Edit, Paste Special.
  4. Choose the Multiply option and click OK.
  5. Delete the cell that contains the 5

2 Code using Paste Special - Multiply

Using Zack Barresse's code from vbax - with minor amendments

This code updates the selected range with a user-entered multiplier

Sub psMultiply()
    ' http://www.vbaexpress.com/kb/getarticle.php?kb_id=47
    Dim y As Long 'The multiplier value, user-defined
    Dim x As Range 'Just a blank cell for variable
    Dim z As Range 'Selection to work with
    Set z = Selection
    y = Application.InputBox("Enter selection multiplier:", _
    Title:="Selection multiplier", Default:=10, Type:=1)
    Set x = Cells(Rows.Count, "A").End(xlUp).Offset(1)
    If y = 0 Then Exit Sub 'Cancel button will = 0, hence cancel
    If x <> "" Then
        Exit Sub
    Else: x.Value = y
        x.Copy
        z.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
        Application.CutCopyMode = False 'Kill copy mode
    End If
    x.ClearContents 'Back to normal
End Sub

Upvotes: 1

Alex P
Alex P

Reputation: 12489

Why not make it a little more generic by only multiplying the numbers you select:

Sub MultiplyByFive()
    Dim cl As Range

    For Each cl In Selection
        cl = cl * 5
    Next cl
End Sub

This way you avoid the 65536 hard coding.

Upvotes: 0

David Peterson
David Peterson

Reputation: 552

you dont need to use function just use these subs:

bellow you can multiplay all of *column A * values:

Sub example()
For a = 1 To 65563
Cells(a, 1).Value = Cells(a, 1).Value * 5
Next a
End Sub

considering 65563 CELLS isn't a good idea, i suggest you to use the sub bellow to count rows and reduce memory usage.

Sub example2()

Dim countrows As Long
   countrows = Range("A" & Rows.Count).End(xlUp).Row
For a = 1 To countrows
Cells(a, 1).Value = Cells(a, 1).Value * 5
Next a
End Sub

Upvotes: 1

Related Questions