I have an array(Variant) of values.
I would like to show them as a list in the excel worksheet.
How do i do this? I am a newbie!!

Hi
Assuming your Variant is called myVariant then
myRows = UBound(myVariant,1)
myColumns = UBound(myVariant,2)

Range("A1").Resize(myRows, myColumns).Value = myVariant

will put your variant in the active sheet with top lefthand corner at
A1.

regards
Paul

thiaga wrote:
I have an array(Variant) of values.
I would like to show them as a list in the excel worksheet.
How do i do this? I am a newbie!!

Do you mean as in Create a List, a feature that was added in xl2003. Or do
you mean just place the values in the worksheet.

If a 2D array in the variable v as an example


rw = Ubound(v,1) - lbound(v,1) + 1
col = Ubound(v,2) - lbound(v,2) + 1

Range("A1").Resize( rw, col).Value = v


for a 1D array to be entered in a column

rw = Ubound(v,1) - lbound(v,1) + 1

Range("A1").Resize(rw,1).Value = Application.Transpose(v)

In xl2000 and earlier, use of transpose limits the size of the array to 5461
elements

http://support.microsoft.com/kb/177991/en-us

. XL: Limitations of Passing Arrays to Excel Using Automation



--
Regards,
Tom Ogilvy


"thiaga" wrote in message
oups.com...
I have an array(Variant) of values.
I would like to show them as a list in the excel worksheet.
How do i do this? I am a newbie!!

Just a note.
This assumes the lowerbound of the array is 1 in both dimensions.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Hi
Assuming your Variant is called myVariant then
myRows = UBound(myVariant,1)
myColumns = UBound(myVariant,2)

Range("A1").Resize(myRows, myColumns).Value = myVariant

will put your variant in the active sheet with top lefthand corner at
A1.

regards
Paul

thiaga wrote:
I have an array(Variant) of values.
I would like to show them as a list in the excel worksheet.
How do i do this? I am a newbie!!

Hi,

Here is the VBA Script.
I am trying to get all the unique items from a particular column in a
sheet and populate it as a list in another sheet. I got the unique
items, but am not able to send them back to the excel as a list or
simply display the values in some range.

Thanks
Thiaga

Public Sub getFundSource()

Dim fundSourceRange As Range
Dim fundSource() As Variant
Dim cnt As Integer
Dim FoundMatch As Boolean

Set fundSourceRange = Sheets("MasterData").Range("G2:G65536")


cnt = 0
For Each Element In fundSourceRange
FoundMatch = False

For i = 1 To cnt
If Element = fundSource(i) Then
FoundMatch = True
End If
Next i

If Not FoundMatch Then
cnt = cnt + 1
ReDim Preserve fundSource(cnt)
fundSource(cnt) = Element
MsgBox (fundSource(cnt))
End If

Next Element

myRows = UBound(fundSource, 1)
myColumns = UBound(fundSource, 2)

Range("A1").Resize(myRows, myColumns).Value = fundSource

End Sub

I am able to show them in the sheet with

'rw = Ubound(v,1) - lbound(v,1) + 1
Range("A1").Resize(rw,1).Value = Application.Transpose(v)
'
How to do it as in Create a List feature?

rw = 20
With Activesheet.Range("A1")
.Value = "Header1"
.Font.Bold = True
End with
Activesheet.Range("A2").Resize(rw,1).Value = Application.Transpose(v)
Activesheet.ListObjects.Add SourceType:=xlSrcRange, _
Source=Range("A1").Resize(rw+1,1)

would be my guess, although I don't have a copy xl2003 handy to test it
with.

for your own interpretation:
http://msdn.microsoft.com/library/en...HV03088209.asp

expand the Add method as it applies to ListObjects

--
Regards,
Tom Ogilvy


"thiaga" wrote in message
oups.com...
I am able to show them in the sheet with

'rw = Ubound(v,1) - lbound(v,1) + 1
Range("A1").Resize(rw,1).Value = Application.Transpose(v)
'
How to do it as in Create a List feature?