Invalid name error

[Bryan]

I have a function that I need to return a string. The problem is that it
does not recognize the name in the spreadsheet. The code is in a module as
follows:

Function ListShorts(ColA As String, Row1 As Integer, ColB As String, Row2 As
Integer) As String

' Given a 2 column range, with part numbers on the left and quantity on the
right, create a string that has
' all the part numbers and quantities.

Dim Holder As String

Holder = Worksheets(1).Cells(ColA, Row1).Value & " -" &
Worksheets(1).Cells(ColB, Row1).Value & "pcs"

For i = (Row1 + 1) To Row2
Holder = Holder & " " & Worksheets(1).Cells(ColA, i).Value & " -" &
Worksheets(1).Cells(ColB, i).Value & "pcs"

Next i

ListShorts = Holder

End Function

The cell formula is as follows:

=ListShorts("B",6,"C",22)

Any ideas what I'm doing wrong? Also, if possible, I need the columns to be
able to grow or shrink, say a maximum of 25, and not glitch on null cells.
I'm not sure how to add that into the VBA. TIA

 

[Gary''s Student]

You may have your rows and columns bas ackwards. Try:

Cells(Row1,ColA)
in place of:
Cells(ColA,Row1)


etc

 

[Bryan]

I don't really understand why that would be the case. I did try it however.
I thought Excel always referred to columns first. Any other ideas or perhaps
an entirely different way of approaching this?

 

[Dave Peterson]

If you use .cells(), it's .cells(somerow,somecol).

But if you use .range(), it's .range(someletter & somenumber)

so maybe you could replace:
Worksheets(1).Cells(ColA, Row1).Value
with
Worksheets(1).range(ColA & Row1).Value

 

[Dave Peterson]

ps. One of the nice thing about using .cells() is that somecol could be a
string (A to IV in xl2003) or a number (1 to 256 in xl2003).

..range() has to be a string concatenated with a number.