CODEWITHSUNDEEP
excelvbaexcel-formula
Riky
Riky

Reputation: 55

Make automatic multiplication in VBA

Hi guys i'm try learning VBA Excel , but i have some problem in my project

Here my code

For p = 10 To 36
    If IsNumeric(Range("E" & p).Value) Then
        E = Range("E" & p).Value
        result2(p) = E * 1000
        Range("E" & p).Value = result2(p)
    End If
Next p

i'm try to make a statement when i insert some numeric the system automatic return value*1000

This is after the program running

enter image description here

it works only once.

Upvotes: 0

Views: 297

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Instead of looping a range you could use Array which is faster:

Sub test()

    Dim arr As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        'Import all values to an array
        arr = .Range("E10:E36")

        'Loop array
        For i = LBound(arr) To UBound(arr)
            'Check if the value is numeric
            If IsNumeric(arr(i, 1)) Then
                'Multiple by 1000
                arr(i, 1) = arr(i, 1) * 1000
            End If
        Next i
        'Import array to the range
        .Range("E10:E36") = arr

    End With

End Sub

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 2006

I think, Problem is Worksheet_change is being called recursively because you are updating the value in same column.

You can handle this by using Application.EnableEvents. This will stop Worksheet_change event and after updating you can set it back to true. 

Application.EnableEvents = False
set value in E column
Application.EnableEvents = True

Or You can simply use the following formula in a separate column (e.g. F) and paste it in entire column.

=IF(ISNUMBER(E2),E2*1000,0)

Upvotes: 2

Beek
Beek

Reputation: 406

It looks like your result2 variable is dim'ed as an integer. in wich case 1000 * 1000 is too large for an integer variable. An integer has to be between -32767 and 32767. So 1 milion can't be put into an integer.

If you change your result2 to a long array you should be okay.

Upvotes: 2

Related Questions