PDA

View Full Version : Solved: extract values in a range



surya prakash
08-19-2009, 10:28 PM
Hi,

I have values 1,2,3,4,5 in (a1 to a5) cells; I am wondering if these values can be extracted using VBA?

thanks

GTO
08-19-2009, 10:48 PM
Greetings Surya,

They can, and through several ways. Could you decribe what the goal is?

Mark

surya prakash
08-19-2009, 11:36 PM
Greeting Mark,
I have following data in Excel sheet in a1 to a5 fields
1
2
3
4
5

I have function as shown below; the idea is to append them in the following format
1,2,3,4,5



Function myJoinList(ByVal myRange As Range) As String



thanks

GTO
08-20-2009, 12:15 AM
Hi again,

As shown, you could use Transpose and Join I believe. In case you may need the vals to come from, let's say, A1:F1 sometime, I think this should grab either. Sorry for the delay - "fuzzy brains" here couldn't get cols/rows in the correct order...

In a new blank WB:

On Sheet1, put some vals in A1:F5

In a Standard Module:

Sub test()
MsgBox myJoinList(ThisWorkbook.Worksheets("Sheet1").Range("A1:A5"))
MsgBox myJoinList(ThisWorkbook.Worksheets("Sheet1").Range("A1:F1"))
MsgBox myJoinList(ThisWorkbook.Worksheets("Sheet1").Range("A1:F5"))
End Sub

Function myJoinList(ByVal myRange As Range) As Variant
Dim aryIn, aryOut
Dim x As Long, y As Long

aryIn = myRange.Value
ReDim aryOut(0 To 0)

For x = LBound(aryIn, 1) To UBound(aryIn, 1)
For y = LBound(aryIn, 2) To UBound(aryIn, 2)
ReDim Preserve aryOut(1 To UBound(aryOut) + 1)
aryOut(UBound(aryOut)) = aryIn(x, y)
Next
Next

myJoinList = Join(aryOut, ",")
End Function


Hope that helps,

Mark

surya prakash
08-20-2009, 12:47 AM
Hi Mark,
Many thanks for your prompt response.
The solution seems to be working; will revert back to you shortly

surya prakash
09-03-2009, 12:10 AM
Hi mark, Many thanks for resolving my post..