Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells range into an array, then into msgbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells range into an array, then into msgbox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells range into an array, then into msgbox
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells range into an array, then into msgbox
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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells range into an array, then into msgbox
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells range into an array, then into msgbox
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display an array in a MsgBox | Excel Programming | |||
How to copy VBA array into range of cells? | Excel Programming | |||
Sum Array Range & Individual Cells | Excel Discussion (Misc queries) | |||
Setting an array range of cells??? | Excel Programming | |||
Problem with array and msgbox | Excel Worksheet Functions |