Invalid name error

  • Thread starter Thread starter Bryan
  • Start date Start date
B

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
 
You may have your rows and columns bas ackwards. Try:

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


etc
 
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?
 
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
 
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.
 
Back
Top