MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need Help

  Asked By: Dana    Date: Dec 08    Category: MS Office    Views: 727

I am very new to VBA and I need 5 miniutes of your
time ,
I have function as below I am calling the function
like .
buf = Array_Unique_Collection([W3:W100)].Value)
The above works absolutely fine
But in place of W100 I want to put some value
dynamically like

count1 = "W100"
and then
buf = Array_Unique_Collection([W3:count1)].Value)

HOW can i accomplish that the substitution does not
work .
Please help me .For experts like you this should be
pretty simple .

Function Array_Unique_Collection(ByVal NotUniqueArry
As Variant) As Variant
'returns unique collection as a 1D array
'returns NULL when there is no value
Dim cTmp As New Collection
Dim i As Long
Dim aTmp As Variant
Dim vElm As Variant

On Error Resume Next
For Each vElm In NotUniqueArry
cTmp.Add CStr(vElm), CStr(vElm)
On Error GoTo 0

If cTmp.Count = 1 And cTmp.Item(1) = vbNullString
Array_Unique_Collection = Null
Exit Function
End If

ReDim aTmp(1 To cTmp.Count)
For i = 1 To cTmp.Count
aTmp(i) = cTmp.Item(i)
Array_Unique_Collection = aTmp
End Function



3 Answers Found

Answer #1    Answered By: Djoser Massri     Answered On: Dec 08

count1 = "W100"
buf = Array_Unique_Collection(Range("W3:" & count1).Value)

you might even like

count1 = 10
buf = Array_Unique_Collection(Range("W3:W" & count1).Value)

Answer #2    Answered By: Sherrie Thomas     Answered On: Dec 08

Try to search out quality VBA Step by Step ebooks on
following sites
www.demonoid.com (click on torrnets)

keywords to be searched

(Name of comp. will provides online tutorials)

I hope u will enjoy browsing these sites

Answer #3    Answered By: Anselma Schmidt     Answered On: Dec 08

Today morning I visit below sites but unfortunately
registrations are closed.

Didn't find what you were looking for? Find more on Need Help Or get search suggestion and latest updates.