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
--
Gary''s Student - gsnu200778


"Bryan" wrote:

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

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?

"Gary''s Student" wrote:

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

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


etc
--
Gary''s Student - gsnu200778


"Bryan" wrote:

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

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



Bryan wrote:

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?

"Gary''s Student" wrote:

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

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


etc
--
Gary''s Student - gsnu200778


"Bryan" wrote:

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

--

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.

Dave Peterson wrote:

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

Bryan wrote:

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?

"Gary''s Student" wrote:

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

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


etc
--
Gary''s Student - gsnu200778


"Bryan" wrote:

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

--

Dave Peterson

--

Dave Peterson