CODEWITHSUNDEEP
excelvbabuttoncalculation
BearCoder
BearCoder

Reputation: 17

VBA Button does not run VBA calculation

I have made some code in VBA and when running it manually it runs just fine. My problem is when assigning the code to a button in Excel it runs the full script, but misses out on a calculation in the script (the column where the calculations is supposed to be at is empty). Anyone who knows why?

My code for the calculation in VBA looks like this and is part of a larger script:

'Calculate Share of portfolio %

secondvalue = WorksheetFunction.Sum(Range("B6:B" & m))

For m = 6 To Rows.Count
    If Cells(m, 2).Value <> "" Then
        Cells(m, 3).Value = Cells(m, 2).Value / secondvalue
    End If
Next m

Upvotes: 0

Views: 64

Answers (1)

BearCoder
BearCoder

Reputation: 17

I figured it out. I think it came down to if I was in the sheet or not. I changed the code to:

With Worksheets("Tabeller")
If WorksheetFunction.CountA(.Range("B6:B" & .Rows.Count)) > 0 Then
    secondvalue = WorksheetFunction.Sum(.Range("B6:B" & m))
    For m = 6 To .Rows.Count
        If .Cells(m, 2).Value <> "" Then
            .Cells(m, 3).Value = .Cells(m, 2).Value / secondvalue
        End If
    Next m
Else
    MsgBox "The range B6:B" & .Rows.Count & " is empty."
End If 
End With

Upvotes: 1

Related Questions