Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

First off, I appreciate any help anyone can offer. I am writing a macro that will give the user a form to input a number key. The form will search a spreadsheet for the key and return the corresponding name attached to that key. The data may have multiple names per key and it will vary depending on the key. I want to loop through the data with .Find and .FindNext, and find all the possible names attached to that key (i have accomplished this part). The part I am having trouble with is during the loop, storing each name in an array that I can pass to another sub. I want to pass the array so that the user can click another command button and cycle through the possible names before choosing one. 

Private Sub FindNameButton_Click()
Dim KeyMatch As Long
Dim NameRow As Long
FindName As Range
KeyMatch = KeyBox.Value ' The UserForm input box

With Worksheets("Master List"). Range("D:D")
Set FindName = .Find(What:= KeyMatch, LookAt:= xlWhole, LookIn:= xlValues,           MatchCase:= False)
If not FindName Is Nothing Then 
FirstAddress = FindName.Address
Do
Application.GoTo FindName
NameRow = ActiveCell.Row
Cells(NameRow, 2).Select 'Selects the name associated with the key identifier
NameBox.Value = ActiveCell.Value 'Fills the UserForm box with the name
' I would like to fill the array here with each name is it passes through but I have   no idea how

NameArray(i) = ActiveCell.Value ' ??????

Set FindName = .FindNext(FindName)
Loop While FindName is Nothing and FristAddress <> FindName.Address
End With
End Sub

Private Sub NextNameButton_Click()
Static cnt As Long
If cnt <= Ubound(NameArray) Then
NameBox.Value = NameArray(cnt) 'Fill UserForm Name Box with name from Name Array

Else
cnt = 0
End If

cnt = cnt + 1 ' increase every time button is clicked
End Sub
share|improve this question

1 Answer

up vote 1 down vote

Your question could use additional details about the problem. A few things I noticed.

  1. You are missing an 'End If' for 'If not FindName Is Nothing Then'
  2. NameArray isn't passed out or into your subroutines. Have you decared NameArray as global?
  3. NameArray needs to be declared as a dynamic array: Dim NameArray() As Variant.
  4. You need to use 'Redim Preserve NameArray(newIndxBound)' to increase the size of an array.
  5. I recommend using 'Option Explicit' to make sure all your variables have been defined.
  6. You might consider using the function StrCmp for string comparison instead of 'FristAddress <> FindName.Address'.

This bit of code that used a global dynamic array might help you out.

Option Explicit

Public MyArray() As Variant


Sub AddToArray()

    Dim indx As Integer

    For indx = 0 To 9
        ReDim Preserve MyArray(indx)
        MyArray(indx) = indx
    Next indx

End Sub


Sub RetrieveFromArray()

    Dim indx As Integer
    Dim sht As Worksheet
    Dim rowN As Integer

    Set sht = ActiveSheet
    rowN = 10
    For indx = 0 To 9
        sht.Cells(rowN, 3) = MyArray(indx)
        rowN = rowN + 1
    Next indx

End Sub
share|improve this answer
+1 for the Option Explicit. That would prevent using FristAddress when you mean FirstAddress. – Doug Glancy Jun 3 at 14:42
Thanks so much this is really helpful. I am sort of a beginner at VBA so pardon my mistakes. ReDim Preserve is a HUGE help, I did not know that existed. I am trying to declare Public NameArray() As Variant as global but I get a compile error every time. I think I have everything except passing to another sub. Is there a different way I should declare this as global, or is there a better way to pass it to the next sub? Thanks again so much for your help so far!!!! – Chris D Jun 3 at 15:18
Thank you for the sample code. When I try to declare Public MyArray() As Variant I get the following error message: Compile Error: Constants, fixed length strings, arrays, user-defined types, and Declare statements not allowed as Public members of object modules. Is this because I am doing this within a userform? If so, is there a way around that or can I pass the userform to a module? Sorry for the beginner level questions! You have been so helpful and I greatly appreciate it! – Chris D Jun 3 at 16:19
You need to declare the array in a code module. With that done, you can still reference the array on code attached to a sheet or form since its global. – Dave Lewis Jun 3 at 21:29
I like to have all my code in modules. Code that ends up in sheets/forms tends to be focused on specific tasks like launching a sub from a command button or returning a range specific to my sheet. Think of any code you put in a sheet or form as belonging to a class which only has scope within the class itself. For example if you added a function to Sheet1 called Calc() then to call it you'd have to do Sheet1.Calc(). If its in a module then you can just call Calc() from anywhere assuming its not private. – Dave Lewis Jun 3 at 21:36
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.