Hi all,

I have a range of cells which are either blank or contain data.
I'd like to create a dynamic(?) array and enter the values of those cells.
Then, after that, i'd like to output the array into a msgbox.
I've started with the below code, but am struggling to get it to work and to
do what i'm required. Can anyone help?
Cheers,
Tony Z.



Sub collate()

Dim N As Long

Arr = Range("F2:H50").Value

For N = LBound(Arr) To UBound(Arr)
impe = impe + Arr(N)
Next N

MsgBox impe

End Sub

Hi Tony

Are the values numerics or text

Sub collate()

'If numerics and you are trying to sum then the below will do
MsgBox Application.Sum(Range("F2:H50"))

'incase these are text
Dim cell As Range,impe As String
For Each cell In Range("F2:H50")
If cell.Text <> "" Then impe = impe & cell.Text
Next
MsgBox impe

End Sub

--
Jacob (MVP - Excel)


"Tony Zappal" wrote:

> Hi all,
>
> I have a range of cells which are either blank or contain data.
> I'd like to create a dynamic(?) array and enter the values of those cells.
> Then, after that, i'd like to output the array into a msgbox.
> I've started with the below code, but am struggling to get it to work and to
> do what i'm required. Can anyone help?
> Cheers,
> Tony Z.
>
>
>
> Sub collate()
>
> Dim N As Long
>
> Arr = Range("F2:H50").Value
>
> For N = LBound(Arr) To UBound(Arr)
> impe = impe + Arr(N)
> Next N
>
> MsgBox impe
>
> End Sub
>

Your description of what you are trying to do is not entirely clear and,
unfortunately, your non-working example code doesn't really clarify it for
us. Can you give us an actual example so we can see what you are hoping to
do? For example, let's say your range is F2:H3... show us some sample values
that you might have in that range and then show us what you want to display
from them in the MessageBox.

--
Rick (MVP - Excel)



"Tony Zappal" <(E-Mail Removed)> wrote in message
news:8E025837-D388-4C54-9F01-(E-Mail Removed)...
> Hi all,
>
> I have a range of cells which are either blank or contain data.
> I'd like to create a dynamic(?) array and enter the values of those cells.
> Then, after that, i'd like to output the array into a msgbox.
> I've started with the below code, but am struggling to get it to work and
> to
> do what i'm required. Can anyone help?
> Cheers,
> Tony Z.
>
>
>
> Sub collate()
>
> Dim N As Long
>
> Arr = Range("F2:H50").Value
>
> For N = LBound(Arr) To UBound(Arr)
> impe = impe + Arr(N)
> Next N
>
> MsgBox impe
>
> End Sub
>

Hi,
Apologies for the vagueness.
Essentially what i am trying to do, is put the cell values in a range into
an array.
The cell values will consist of numbers, 8 characters long.
I then want to build a string from the values in the array, seperated by a
comma.
That string i then want to enter into a msgbox amongst other things.
(Included the code example of what i have done so far to show i'm trying )

Cheers,
Tony Z.


"Rick Rothstein" wrote:

> Your description of what you are trying to do is not entirely clear and,
> unfortunately, your non-working example code doesn't really clarify it for
> us. Can you give us an actual example so we can see what you are hoping to
> do? For example, let's say your range is F2:H3... show us some sample values
> that you might have in that range and then show us what you want to display
> from them in the MessageBox.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Tony Zappal" <(E-Mail Removed)> wrote in message
> news:8E025837-D388-4C54-9F01-(E-Mail Removed)...
> > Hi all,
> >
> > I have a range of cells which are either blank or contain data.
> > I'd like to create a dynamic(?) array and enter the values of those cells.
> > Then, after that, i'd like to output the array into a msgbox.
> > I've started with the below code, but am struggling to get it to work and
> > to
> > do what i'm required. Can anyone help?
> > Cheers,
> > Tony Z.
> >
> >
> >
> > Sub collate()
> >
> > Dim N As Long
> >
> > Arr = Range("F2:H50").Value
> >
> > For N = LBound(Arr) To UBound(Arr)
> > impe = impe + Arr(N)
> > Next N
> >
> > MsgBox impe
> >
> > End Sub
> >
> .
>

I'd still like to see the example I asked for. Why? So I can see the order
you want the items listed in and whether you actually want all the data on
one line as your last post seems to indicate ("I then want to build a string
from the values in the array, separated by a comma") or if you want the
cells across to be comma separated but show each row on a separate line (or,
perhaps, something else altogether).

--
Rick (MVP - Excel)



"Tony Zappal" <(E-Mail Removed)> wrote in message
news:ADD3FCCD-1656-467E-A728-(E-Mail Removed)...
> Hi,
> Apologies for the vagueness.
> Essentially what i am trying to do, is put the cell values in a range into
> an array.
> The cell values will consist of numbers, 8 characters long.
> I then want to build a string from the values in the array, seperated by a
> comma.
> That string i then want to enter into a msgbox amongst other things.
> (Included the code example of what i have done so far to show i'm trying
> )
>
> Cheers,
> Tony Z.
>
>
> "Rick Rothstein" wrote:
>
>> Your description of what you are trying to do is not entirely clear and,
>> unfortunately, your non-working example code doesn't really clarify it
>> for
>> us. Can you give us an actual example so we can see what you are hoping
>> to
>> do? For example, let's say your range is F2:H3... show us some sample
>> values
>> that you might have in that range and then show us what you want to
>> display
>> from them in the MessageBox.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>>
>> "Tony Zappal" <(E-Mail Removed)> wrote in message
>> news:8E025837-D388-4C54-9F01-(E-Mail Removed)...
>> > Hi all,
>> >
>> > I have a range of cells which are either blank or contain data.
>> > I'd like to create a dynamic(?) array and enter the values of those
>> > cells.
>> > Then, after that, i'd like to output the array into a msgbox.
>> > I've started with the below code, but am struggling to get it to work
>> > and
>> > to
>> > do what i'm required. Can anyone help?
>> > Cheers,
>> > Tony Z.
>> >
>> >
>> >
>> > Sub collate()
>> >
>> > Dim N As Long
>> >
>> > Arr = Range("F2:H50").Value
>> >
>> > For N = LBound(Arr) To UBound(Arr)
>> > impe = impe + Arr(N)
>> > Next N
>> >
>> > MsgBox impe
>> >
>> > End Sub
>> >
>> .
>>

Oh, and did you need that array for anything else or were you using it
**only** to be able to iterate through it in order to fill in the
MessageBox?

--
Rick (MVP - Excel)



"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:uvM$(E-Mail Removed)...
> I'd still like to see the example I asked for. Why? So I can see the order
> you want the items listed in and whether you actually want all the data on
> one line as your last post seems to indicate ("I then want to build a
> string from the values in the array, separated by a comma") or if you want
> the cells across to be comma separated but show each row on a separate
> line (or, perhaps, something else altogether).
>
> --
> Rick (MVP - Excel)
>
>
>
> "Tony Zappal" <(E-Mail Removed)> wrote in message
> news:ADD3FCCD-1656-467E-A728-(E-Mail Removed)...
>> Hi,
>> Apologies for the vagueness.
>> Essentially what i am trying to do, is put the cell values in a range
>> into
>> an array.
>> The cell values will consist of numbers, 8 characters long.
>> I then want to build a string from the values in the array, seperated by
>> a
>> comma.
>> That string i then want to enter into a msgbox amongst other things.
>> (Included the code example of what i have done so far to show i'm trying
>> )
>>
>> Cheers,
>> Tony Z.
>>
>>
>> "Rick Rothstein" wrote:
>>
>>> Your description of what you are trying to do is not entirely clear and,
>>> unfortunately, your non-working example code doesn't really clarify it
>>> for
>>> us. Can you give us an actual example so we can see what you are hoping
>>> to
>>> do? For example, let's say your range is F2:H3... show us some sample
>>> values
>>> that you might have in that range and then show us what you want to
>>> display
>>> from them in the MessageBox.
>>>
>>> --
>>> Rick (MVP - Excel)
>>>
>>>
>>>
>>> "Tony Zappal" <(E-Mail Removed)> wrote in message
>>> news:8E025837-D388-4C54-9F01-(E-Mail Removed)...
>>> > Hi all,
>>> >
>>> > I have a range of cells which are either blank or contain data.
>>> > I'd like to create a dynamic(?) array and enter the values of those
>>> > cells.
>>> > Then, after that, i'd like to output the array into a msgbox.
>>> > I've started with the below code, but am struggling to get it to work
>>> > and
>>> > to
>>> > do what i'm required. Can anyone help?
>>> > Cheers,
>>> > Tony Z.
>>> >
>>> >
>>> >
>>> > Sub collate()
>>> >
>>> > Dim N As Long
>>> >
>>> > Arr = Range("F2:H50").Value
>>> >
>>> > For N = LBound(Arr) To UBound(Arr)
>>> > impe = impe + Arr(N)
>>> > Next N
>>> >
>>> > MsgBox impe
>>> >
>>> > End Sub
>>> >
>>> .
>>>

Hi Rick,
I've done some work overnight, and have come up with this code.
It looks like it's doing what I want now. Just looks a bit rough.
I'm putting it into a msgbox to show the user what has been put into the
array.
The output is then going to be used to perform an sql query on an oracle
database.
The output from the query will be in ascending order, so it doesn't matter
what the order is in the array.

Cheers,
Tony Z

-------------------

Sub collate2()

Dim Arr() As Long
Dim N As Long
Dim c As Range

Range("F2:H40").Select

i = 0

For Each c In Selection
If c.Value <> Empty Then
i = i + 1
End If
Next c

ReDim Arr(1 To i)

i = 0

For Each c In Selection
If c.Value <> Empty Then
i = i + 1
Arr(i) = c.Value
End If
Next c

For N = LBound(Arr) To UBound(Arr)
Arrbuild = Arr(N) & "," & Arrbuild
Next N
Arrbuild = Left(Arrbuild, Len(Arrbuild) - 1)

MsgBox Arrbuild

Range("A1").Select

End Sub



"Rick Rothstein" wrote:

> Oh, and did you need that array for anything else or were you using it
> **only** to be able to iterate through it in order to fill in the
> MessageBox?
>
> --
> Rick (MVP - Excel)
>
>
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:uvM$(E-Mail Removed)...
> > I'd still like to see the example I asked for. Why? So I can see the order
> > you want the items listed in and whether you actually want all the data on
> > one line as your last post seems to indicate ("I then want to build a
> > string from the values in the array, separated by a comma") or if you want
> > the cells across to be comma separated but show each row on a separate
> > line (or, perhaps, something else altogether).
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> >
> > "Tony Zappal" <(E-Mail Removed)> wrote in message
> > news:ADD3FCCD-1656-467E-A728-(E-Mail Removed)...
> >> Hi,
> >> Apologies for the vagueness.
> >> Essentially what i am trying to do, is put the cell values in a range
> >> into
> >> an array.
> >> The cell values will consist of numbers, 8 characters long.
> >> I then want to build a string from the values in the array, seperated by
> >> a
> >> comma.
> >> That string i then want to enter into a msgbox amongst other things.
> >> (Included the code example of what i have done so far to show i'm trying
> >> )
> >>
> >> Cheers,
> >> Tony Z.
> >>
> >>
> >> "Rick Rothstein" wrote:
> >>
> >>> Your description of what you are trying to do is not entirely clear and,
> >>> unfortunately, your non-working example code doesn't really clarify it
> >>> for
> >>> us. Can you give us an actual example so we can see what you are hoping
> >>> to
> >>> do? For example, let's say your range is F2:H3... show us some sample
> >>> values
> >>> that you might have in that range and then show us what you want to
> >>> display
> >>> from them in the MessageBox.
> >>>
> >>> --
> >>> Rick (MVP - Excel)
> >>>
> >>>
> >>>
> >>> "Tony Zappal" <(E-Mail Removed)> wrote in message
> >>> news:8E025837-D388-4C54-9F01-(E-Mail Removed)...
> >>> > Hi all,
> >>> >
> >>> > I have a range of cells which are either blank or contain data.
> >>> > I'd like to create a dynamic(?) array and enter the values of those
> >>> > cells.
> >>> > Then, after that, i'd like to output the array into a msgbox.
> >>> > I've started with the below code, but am struggling to get it to work
> >>> > and
> >>> > to
> >>> > do what i'm required. Can anyone help?
> >>> > Cheers,
> >>> > Tony Z.
> >>> >
> >>> >
> >>> >
> >>> > Sub collate()
> >>> >
> >>> > Dim N As Long
> >>> >
> >>> > Arr = Range("F2:H50").Value
> >>> >
> >>> > For N = LBound(Arr) To UBound(Arr)
> >>> > impe = impe + Arr(N)
> >>> > Next N
> >>> >
> >>> > MsgBox impe
> >>> >
> >>> > End Sub
> >>> >
> >>> .
> >>>
> .
>

As long as order doesn't matter, I would consider eliminating the array
altogether and doing it one of the following ways depending on what kind of
values the cells in the range can have...

If the values in the cells could have embedded spaces
===============================================
Sub JoinRange()
Dim R As Range, Msg As String
For Each R In Range("F2:H40").Columns
Msg = Msg & "," & Join(WorksheetFunction.Transpose(R), ",")
Next
Do While InStr(Msg, ",,")
Msg = Replace(Msg, ",,", ",")
Loop
If Left(Msg, 1) = "," Then Msg = Mid(Msg, 2)
If Right(Msg, 1) = "," Then Msg = Left(Msg, Len(Msg) - 1)
MsgBox Msg
End Sub

If the values in the cells will NEVER have embedded spaces
===============================================
Sub JoinRange()
Dim R As Range, Msg As String
For Each R In Range("F2:H20").Columns
Msg = Msg & " " & Join(WorksheetFunction.Transpose(R), " ")
Next
Msg = Replace(WorksheetFunction.Trim(Msg), " ", ",")
MsgBox Msg
End Sub

--
Rick (MVP - Excel)



"Tony Zappal" <(E-Mail Removed)> wrote in message
news:BD2CB1E7-A0FA-47CC-959A-(E-Mail Removed)...
> Hi Rick,
> I've done some work overnight, and have come up with this code.
> It looks like it's doing what I want now. Just looks a bit rough.
> I'm putting it into a msgbox to show the user what has been put into the
> array.
> The output is then going to be used to perform an sql query on an oracle
> database.
> The output from the query will be in ascending order, so it doesn't matter
> what the order is in the array.
>
> Cheers,
> Tony Z
>
> -------------------
>
> Sub collate2()
>
> Dim Arr() As Long
> Dim N As Long
> Dim c As Range
>
> Range("F2:H40").Select
>
> i = 0
>
> For Each c In Selection
> If c.Value <> Empty Then
> i = i + 1
> End If
> Next c
>
> ReDim Arr(1 To i)
>
> i = 0
>
> For Each c In Selection
> If c.Value <> Empty Then
> i = i + 1
> Arr(i) = c.Value
> End If
> Next c
>
> For N = LBound(Arr) To UBound(Arr)
> Arrbuild = Arr(N) & "," & Arrbuild
> Next N
> Arrbuild = Left(Arrbuild, Len(Arrbuild) - 1)
>
> MsgBox Arrbuild
>
> Range("A1").Select
>
> End Sub
>
>
>
> "Rick Rothstein" wrote:
>
>> Oh, and did you need that array for anything else or were you using it
>> **only** to be able to iterate through it in order to fill in the
>> MessageBox?
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>>
>> "Rick Rothstein" <(E-Mail Removed)> wrote in message
>> news:uvM$(E-Mail Removed)...
>> > I'd still like to see the example I asked for. Why? So I can see the
>> > order
>> > you want the items listed in and whether you actually want all the data
>> > on
>> > one line as your last post seems to indicate ("I then want to build a
>> > string from the values in the array, separated by a comma") or if you
>> > want
>> > the cells across to be comma separated but show each row on a separate
>> > line (or, perhaps, something else altogether).
>> >
>> > --
>> > Rick (MVP - Excel)
>> >
>> >
>> >
>> > "Tony Zappal" <(E-Mail Removed)> wrote in message
>> > news:ADD3FCCD-1656-467E-A728-(E-Mail Removed)...
>> >> Hi,
>> >> Apologies for the vagueness.
>> >> Essentially what i am trying to do, is put the cell values in a range
>> >> into
>> >> an array.
>> >> The cell values will consist of numbers, 8 characters long.
>> >> I then want to build a string from the values in the array, seperated
>> >> by
>> >> a
>> >> comma.
>> >> That string i then want to enter into a msgbox amongst other things.
>> >> (Included the code example of what i have done so far to show i'm
>> >> trying
>> >> )
>> >>
>> >> Cheers,
>> >> Tony Z.
>> >>
>> >>
>> >> "Rick Rothstein" wrote:
>> >>
>> >>> Your description of what you are trying to do is not entirely clear
>> >>> and,
>> >>> unfortunately, your non-working example code doesn't really clarify
>> >>> it
>> >>> for
>> >>> us. Can you give us an actual example so we can see what you are
>> >>> hoping
>> >>> to
>> >>> do? For example, let's say your range is F2:H3... show us some sample
>> >>> values
>> >>> that you might have in that range and then show us what you want to
>> >>> display
>> >>> from them in the MessageBox.
>> >>>
>> >>> --
>> >>> Rick (MVP - Excel)
>> >>>
>> >>>
>> >>>
>> >>> "Tony Zappal" <(E-Mail Removed)> wrote in message
>> >>> news:8E025837-D388-4C54-9F01-(E-Mail Removed)...
>> >>> > Hi all,
>> >>> >
>> >>> > I have a range of cells which are either blank or contain data.
>> >>> > I'd like to create a dynamic(?) array and enter the values of those
>> >>> > cells.
>> >>> > Then, after that, i'd like to output the array into a msgbox.
>> >>> > I've started with the below code, but am struggling to get it to
>> >>> > work
>> >>> > and
>> >>> > to
>> >>> > do what i'm required. Can anyone help?
>> >>> > Cheers,
>> >>> > Tony Z.
>> >>> >
>> >>> >
>> >>> >
>> >>> > Sub collate()
>> >>> >
>> >>> > Dim N As Long
>> >>> >
>> >>> > Arr = Range("F2:H50").Value
>> >>> >
>> >>> > For N = LBound(Arr) To UBound(Arr)
>> >>> > impe = impe + Arr(N)
>> >>> > Next N
>> >>> >
>> >>> > MsgBox impe
>> >>> >
>> >>> > End Sub
>> >>> >
>> >>> .
>> >>>
>> .
>>