Invalid name error



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

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:


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:

in place of:



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
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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question