VBA returns error "Sub or Function undefined"

Question

Function tenderSummary()
    For i = 5 To 10
        If cell(i, 66) <> "" Then
            If Range("TenderPrice") <> "" Then
                Range("TenderTicker").Copy cell(i, 66)
                Range("Quantity").Copy cell(i, 67)
                Range("TenderPrice").Copy cell(i, 68)
                Range("Start").Copy cell(i, 69)
                Range("End").Copy cell(i, 70)
            End If
            If Range("TenderPrice2") <> "" Then
                Range("TenderTicker2").Copy cell(i, 66)
                Range("Quantity2").Copy cell(i, 67)
                Range("TenderPrice2").Copy cell(i, 68)
                Range("Start_2").Copy cell(i, 69)
                Range("End_2").Copy cell(i, 70)
            End If
            If Range("TenderPrice3") <> "" Then
                Range("TenderTicker3").Copy cell(i, 66)
                Range("Quantity3").Copy cell(i, 67)
                Range("TenderPrice3").Copy cell(i, 68)
                Range("Start_3").Copy cell(i, 69)
                Range("End_3").Copy cell(i, 70)
            End If
        End If
    Next
End Function

For the above code, I keep getting "Sub or Function not defined", could anyone explain why?

Essentially, I have my variables: TenderPrice, TenderTicker, Quantity, Start, End (same for TenderPrice2,...,End2, and those marked with 3), and these variables are dynamic in nature, meaning that they disappear after around 20s or so.

My task is to build a tender summary to keep these tenders in place as static data, and the above is what I have written - to copy paste in the first line, and skip the first line if there's data in it already to the second and so on.

How can I fix this problem?

It's cellS not cell. Search for cell( replace with cells(. They should all be capitalized automagically. — user4039065
– user4039065, Commented Sep 30, 2018 at 5:28
Btw, those Cells(r, c) references should all have a valid parent worksheet reference and you should be using ElseIf to avoid redundant calculation. — user4039065
– user4039065, Commented Sep 30, 2018 at 5:31

score 1 · Accepted Answer · 2018-09-30 06:04:42Z

1

The Range.Cells property is plural; e.g. Cells not Cell.
There is no valid parent worksheet reference for any of the Cells(r, c) references. You've provided no information as to where the named ranges reside. If they are on the same worksheet, they could be included in a With ... End With block.
Using ElseIf will reduce redundant calculation.

Code rewrite:

Function tenderSummary()

    With worksheets("model")

        For i = 5 To 10
            If .Cells(i, 66) <> "" Then
                If Range("TenderPrice") <> "" Then
                    Range("TenderTicker").Copy .Cells(i, 66)
                    Range("Quantity").Copy .Cells(i, 67)
                    Range("TenderPrice").Copy .Cells(i, 68)
                    Range("Start").Copy .Cells(i, 69)
                    Range("End").Copy .Cells(i, 70)
                ElseIf Range("TenderPrice2") <> "" Then
                    Range("TenderTicker2").Copy .Cells(i, 66)
                    Range("Quantity2").Copy .Cells(i, 67)
                    Range("TenderPrice2").Copy .Cells(i, 68)
                    Range("Start_2").Copy .Cells(i, 69)
                    Range("End_2").Copy .Cells(i, 70)
                ElseIf Range("TenderPrice3") <> "" Then
                    Range("TenderTicker3").Copy .Cells(i, 66)
                    Range("Quantity3").Copy .Cells(i, 67)
                    Range("TenderPrice3").Copy .Cells(i, 68)
                    Range("Start_3").Copy .Cells(i, 69)
                    Range("End_3").Copy .Cells(i, 70)
                End If
            End If
        Next

    End With

End Function

edited Sep 30, 2018 at 6:04

answered Sep 30, 2018 at 5:42

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

user4039065 Over a year ago

Named ranges (e.g. Range("TenderPrice")) often produce less problems when they have a valid worksheet parent reference; even when the named range is workbook scope.

Collectives™ on Stack Overflow

VBA returns error "Sub or Function undefined"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related