Calculating Recurrent Values in VBA

Question

I am struggling with creating a VBA Function to calculate recurrences of a value after a specified value as been reached. In my spreadsheet I am looking to iterate through the rows of Column A of a spreadsheet, and when getting to a value, in my instance the identification number of a switch, add up recurrent values below it that match my credential;

Public Function CalculatePS(Ref As String)
    Dim i As Long
    Dim j As Long
    Dim c1 As Long
    Dim c2 As Long
    Worksheets("Sheet1").Activate
    Set Range = ActiveWorksheet.Columns("A")
    For i = 1 To Range.End
        If Right(Cells(i, "A").Value, 4) < Ref + 1000 Then
            j = i + 1
            ActiveCell = Cells(j, "A")
            Do While ActiveCell.Value <> Empty
                If ActiveCell.Value = psType Then
                    c1 = c1 + 1
                End If
                If ActiveCell.Value = psType" Then
                    c2 = c2 + 1
                End If
            Wend
            i = j + 1
       End If

    Next i
    MsgBox (c1)
    MsgBox (c2)
    Return c1
End Function

I had hoped this function would iterate through the rows until Right(Cells(i,"A).Value,4) was equal to the reference number I was looking for in identifying switches on our network. I am fairly new to VBA and am sure I could do this in C, but cannot make it function in this environment. Currently, I am getting the error "argument not optional" when trying to compile with CalculatePS("2000").

Just to clarify, I have a list of switch names, and the power supplies assigned to them. with that I am trying to calculate how many 715w and 1100w power supplies are in all switches with the desired identification number.

This is an example of the spreadsheet:

biot-b348-uxxxx
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
BlankCell
biot-b348-uxxxx
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
BlankCell

Thank you!

Editted code:

    Public Function CalculatePS(uNumber As String, psType As String) As Integer

    Dim i As Long
    Dim j As Long
    Dim c1 As Long
    Worksheets("Sheet1").Activate
    For i = 1 To rows.End
    If Right(Cells(i, "A").Value, 4) < uNumber + 1000 And Right(Cells(i, "A").Value, 4) > uNumber Then
        j = i + 1
        Do While Cells(j,"A").Value <> Empty
            If  Cells(j,"A").Value = psType Then
                c1 = c1 + 1
            End If
            Loop
            i = j + 1
        End If

    Next i
    CalculatePS = c1

   End Function

`Return` isn't used to return values in VBA. Assigning to the function name is used instead. `Return` is a legacy statement which returns *control* (not values) to the caller. There is no reason to use it in modern VBA (use end sub or exit sub (or function) instead) — John Coleman, Oct 28 '16 at 18:55
@JohnColeman means instead of `Return c1` you should have `CalculatePS = c1`. — J Reid, Oct 28 '16 at 19:00
I have changed the code to what I believe is correct, but still recieve the error, Arguement is not Optional. I have tried to call `=CalculatePS("2000","C3KX-PWR-1100WAC")` @JohnColeman @BruceWayne — Miles, Oct 28 '16 at 19:07
Try to [avoid using `.ActiveCell`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). `ActiveCell =- Cells(j,"A")` sets the activecell to whatever value `Cells(j,"A")` has in it. Is that what you intended, or did you want to make the `ActiveCell` `Cells(j,"A")`? — BruceWayne, Oct 28 '16 at 19:12
`Range` isn't declared locally, so it resolves to the global `Range`, which requires an address. Do something like `Dim rng As Range` and use that instead. Also, `ActiveWorksheet` should be `ActiveSheet`, and `Range.End` is another argument not optional error (see [How can I find last row that contains data in the Excel sheet with a macro?](http://stackoverflow.com/q/71180/4088852)). You likely have other errors too, but I'm not sure what this is intended to do. — Comintern, Oct 28 '16 at 19:13
@Comintern The goal is to search a column for the correct identification number for our switch spreadsheet, then add up the powersupplies that are in the list under it. I have changed the code as you recommended, but am still getting the same error. Does the main if statement make sense? I am looking for "u" numbers that are between 2000 and 3000. in my example. — Miles, Oct 28 '16 at 19:21
@BruceWayne I have adjusted this as you recommended. My goal was to assign the ActiveCell to be a pointer to the cell and not the value in the cell itself. Still stuck with the same issue of the optional arguements — Miles, Oct 28 '16 at 19:21
It's actually *not* the same optional arguments issue. It should now be on the `rng.End` line. See the link in my comment above. — Comintern, Oct 28 '16 at 19:33
@BruceWayne I have made a lot of edits, I believe this is has gotten rid of the optional arguement error. I am not just getting an incorrect value return. — Miles, Oct 28 '16 at 19:33
Try stepping through the macro with `F8` (first put a break near the top of the function, so it stops running automatically) and see if your values are set as you're expecting. Perhaps a loop is incorrect? — BruceWayne, Oct 28 '16 at 20:22

score 0 · Answer 1 · answered Oct 29 '16 at 11:01

Typically i would prefer to separate the task into a few other columns. For example, one column could be right(A1, 4) to extract the unique number uXXXX, and after having sufficient columns to cover all your conditions, you can use a simple countif statement to achieve what you want. I am assuming that you do not have the liberty to make changes to the format of your worksheet.

Also, it is obvious from your syntax that you probably have never written in VBA, so i changed your code a little. Once again, assuming you do not have the liberty to use multiple columns to achieve what you want, the following function may help (=CalculatePS(A:A,"1234")).

'@param vInputs variant type for your to pass range containing your input (in your case, select A:A
'@param strUID string type representing the unique number, XXXX, in uXXXX (note to pass as "XXXX" instead of XXXX)

Public Function CalculatePS(vInputs As Variant, strUID As String) As String
    Dim vTemp As Variant: vTemp = vInputs
    Dim intCounterTotal As Long
    Dim intCounterRelevant As Long
    Dim intNumOf715 As Integer: intNumOf715 = 0
    Dim intNumOf1100 As Integer: intNumOf1100 = 0

    For intCounterTotal = 1 To UBound(vTemp, 1) - 1
        If CStr(Right(vTemp(intCounterTotal, 1), 4)) = CStr(strUID) Then
            For intCounterRelevant = intCounterTotal + 1 To UBound(vTemp, 1) - 1
                If Trim(vTemp(intCounterRelevant, 1)) = vbNullString Then GoTo FinishComputation
                If InStr(vTemp(intCounterRelevant, 1), "715WAC") > 0 Then intNumOf715 = intNumOf715 + 1
                If InStr(vTemp(intCounterRelevant, 1), "1100WAC") > 0 Then intNumOf1100 = intNumOf1100 + 1
            Next intCounterRelevant
        End If
    Next intCounterTotal
FinishComputation:
    CalculatePS = "Number of 715s: " & intNumOf715 & "; Number of 1100s: " & intNumOf1100
End Function

Test Data:

biot-b348-uxxxx
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
BlankCell
biot-b348-u1234
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
C3KX-PWR-1100WAC
C3KX-PWR-715WAC
C3KX-PWR-715WAC
BlankCell

Calculating Recurrent Values in VBA

1 Answers1