0

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
GSerg
  • 76,472
  • 17
  • 159
  • 346
BearCoder
  • 21
  • 3
  • 2
    Where do you assign a value to `m` before `secondvalue = WorksheetFunction.Sum(Range("B6:B" & m))`? – BigBen Feb 03 '23 at 13:32
  • 2
    Btw, looping to `Rows.Count` is likely very inefficient. – BigBen Feb 03 '23 at 13:33
  • BigBen - I assign the value to m further up in my script. I have posted an answer I found useful. I am looping because I have some other columns that may change in number of rows so the calculations also has to change. I dont no if and how to do it more efficient – BearCoder Feb 03 '23 at 13:47
  • 1
    Just gave you the [link](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) to make it more efficient! – BigBen Feb 03 '23 at 13:47
  • Like mentioned before `secondvalue ` is not in the loop so it will never change. – Davesexcel Feb 03 '23 at 13:59

1 Answers1

1

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
BearCoder
  • 21
  • 3
  • 2
    I suggest you [find the last row](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) instead of looping to `.Rows.Count`. – BigBen Feb 03 '23 at 13:47