location from array

[synergy16]

Hello all,

i have 2 listboxes. one is populated by a command button, it sets a list of vendors. from that box a the user can click on a vendor of choice, the second listbox will then populate with the parts that are at the chosen vendor. (both are dynamically created arrays)

my problem........i am looking to get the ROW cell location (not the value in the cell) for the clicked on value in the second box and i cant see to get the code correct.

any help would be appreciated.

 

[Jerry Sullivan]

Hi synergy16,

Are you using VBA in your current setup or just the Listbox functionality?

Either way you can use the Match function to find the relative row number of the selected item within the DNR (dynamic named range). Using the range address of the DNR, plus the relative row location of the selected item, you can find the address of the selected item. The coding or formula would be different depending on whether you are using VBA, but the concept would be the same.

 

[synergy16]

the whole project is done in VBA

 

[Jerry Sullivan]

Try using Application.Match and Application.Index to find the cell, then Range.Address to get its address.

Just post the code you are trying if you aren't able to work that out and want some help.

 

[synergy16]

here is my code. i realize its not very elegant, VBA isn't the language i learned to program in. (C++ was)

Option ExplicitPublic venPicked As Variant
Public cntr3  As Integer
Dim serialNum() As Variant
Private Sub Workbook_Open()
UserForm1.Show
End Sub
'*************************************THIS SUB FINDS THE CELL LOCATION OF THE PART NUMBER PICKED**************************************
Private Sub partNumLB_Click()


'***************THIS IS WHERE I WANT TO FIND THE CELL LOCATION OF THE CLICKED ON VARIABLE*************************


End Sub




'****************************THIS SUB CREATES A LIST OF VENDORS TO CHOOSE FROM*****************************************************
Private Sub whatsAtVenCB_Click()
Dim vendors() As Variant


'get number of populated rows total
Dim data As Range
Dim r As Variant
Dim k As Variant


    Set data = Sheet1.Range("i1:i" & Range("i" & Rows.Count).End(xlUp).Row)
        cntr3 = 0
     For Each r In data
          cntr3 = cntr3 + 1
               If r.Offset(1) = vbNullString And r.Offset(2) = vbNullString Then
               End If
     Next r
'resize


ReDim vendors(cntr3)


    For k = 1 To cntr3
        vendors(k) = Cells(k, 9)
    Next
  'remove the duplicate PO numbers
Dim outK2 As Integer, inK2 As Integer, newK2 As Integer, Found As Boolean, vendorsNoDup() As Variant
    outK2 = 2
    newK2 = 2
    ReDim vendorsNoDup(1 To UBound(vendors))
    vendorsNoDup(1) = vendors(1)
    While outK2 <= UBound(vendors)
        Found = False
        inK2 = 1
        Do While inK2 <= UBound(vendors)
            If vendors(outK2) = vendorsNoDup(inK2) Then
                Found = True
                Exit Do
            End If
            inK2 = inK2 + 1
        Loop
        If Not Found Then
            vendorsNoDup(newK2) = vendors(outK2)
            newK2 = newK2 + 1
        End If
        outK2 = outK2 + 1
    Wend


vendorLB.List = vendorsNoDup
End Sub


'***************************THIS SUB WILL POPULATE THE CURRENT PART NUMBERS AT A SPECIFIC VENDOR********************************
Private Sub vendorLB_Click()
Dim prtNumsV() As Variant
Erase prtNumsV
venPicked = vendorLB.Value
arrivedTb.Text = ""
Dim cntrV, t, y, p, venCounter As Integer
'get number of populated rows total
    Dim data As Range
    Dim r As Variant
    Dim k As Variant


        Set data = Sheet1.Range("b1:b" & Range("b" & Rows.Count).End(xlUp).Row)
                cntrV = 0
            For Each r In data
                cntrV = cntrV + 1
                    If r.Offset(1) = vbNullString And r.Offset(2) = vbNullString Then
                    End If
            Next r
'get number of times selected customer appears on sheet
    y = 0
    venCounter = 0
    For t = 2 To cntrV + 2
        If venPicked = Cells(t, 9) Then
            venCounter = venCounter + 1
        End If
    Next


   On Error Resume Next


'populate partnumber listbox, excluding parts that have alreay arrived back at hexcel
    ReDim prtNumsV(venCounter)
     ReDim serialNum(venCounter)
        t = 0
        p = 0
    For t = 2 To cntrV + 2
        If venPicked = Cells(t, 9) And UCase(Left(Cells(t, 12), 7)) <> "ARRIVED" Then
            prtNumsV(p) = Cells(t, 3).Value
            serialNum(p) = Cells(t, 6).Value
            p = p + 1
        End If
    Next
    If p = 0 Then
        arrivedTb.Text = "NO PARTS AT VENDOR"
    End If


partNumLB.List = prtNumsV


End Sub

 

[synergy16]

i understand what the range function does, i just cant get the syntax correct

 

[Jerry Sullivan]

Here's an example of how to use Match, Index and Range.Address to return that location.

'*************************************THIS SUB FINDS THE CELL LOCATION OF THE PART NUMBER PICKED**************************************
Private Sub partNumLB_Click()
 
 Dim sSelectedPart As String
 Dim rPartList As Range, sMatchedCellAddress As String
 Dim vMatchRowIndex As Variant
 
 With Sheet1
   '--get range with part numbers
   Set rPartList = Range(.Cells(1, "C"), .Cells(.Rows.Count, "C").End(xlUp))
 End With
 
 '--get user selected value
 sSelectedPart = partNumLB.Value

 '--get relative row number within part number list range
 vMatchRowIndex = Application.Match(sSelectedPart, rPartList, 0)
 
 If IsNumeric(vMatchRowIndex) Then
   sMatchedCellAddress = Application.Index(rPartList, CLng(vMatchRowIndex)).Address
   MsgBox "Match found at address: " & sMatchedCellAddress
   
 Else
   MsgBox "Match not found"
 End If
 
End Sub

 

[synergy16]

perfect. thank you so much Jerry