Array formating

[Shogun]

Im having trouble with part of this. I need to take values from column A and put them into an array that will be passed to another program. But the catch is when its passed i need the values to be with special formating. The range of Values are dynamic and can be anywhere from 3 to a couple thousand.

So after i have the values i need to send them back like this: ('value1','value2','value3')

Is this easy to do or am i stuck?

 

[pgc01]

Hi

Maybe this example helps?

Sub Test()
Dim sArray(1 To 4) As String
 
sArray(1) = "Test1"
sArray(2) = "Test2"
sArray(3) = "Test3"
sArray(4) = "Test4"
 
MsgBox "('" & Join(sArray, "','") & "')"
End Sub

 

[Shogun]

This is working the way i need but now how can i make array dynamic from a range that will still work with a join?

Dim sArray As Variant
LastRow = Range("O" & Rows.Count).End(xlUp).ROW
sArray = Range("O1:O" & LastRow).Value
MsgBox "('" & Join(sArray, "','") & "')"

 

[pgc01]

Hi

Try:

Sub Test()
Dim r As Range
 
Set r = Range("O1", Range("O" & Rows.Count).End(xlUp))
MsgBox "('" & Join(Application.Transpose(r), "','") & "')"
End Sub

 

[Shogun]

O so close this works the way i want it to but when i use in dosent include the end ")" for some reason.

Dim objOptTab As Object
Dim r As Range

objOptTab.FreeTable
Set r = Range("O1", Range("O" & Rows.Count).End(xlUp))
 
objOptTab.Rows.Add
objOptTab(objOptTab.RowCount, "TEXT") = "MATNR IN ('" & Join(Application.Transpose(r), "','") & "')"

For some reason its only showing as : Data(1,1) : "MATNR IN ('Value1','Value2','Value3'" : Variant/String

 

[pgc01]

Sorry, cannot help you there. The difference I see between your code and the one I posted is that you are using the object objOptTab, and I don't know that object.

 

[Shogun]

I figured it out I needed to pass the join to a variant and then call that in the objects line. But it was all a waste there is a character limit so I had to switch to a loop thru cells one at a time instead of writing all in to one long join string. But thanks for the help.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[pgc01]

I'm glad you have your problem solved. Cheers!