Hi Guys
Thanks in advance fro taking the time to look at this much appreciated.
I have a worksheet called sites, each site has a ref no found in column A and be in this coloumn multiple times because each site also has a unit number found in column B the reason for this is some sites have multiple units or blocks,
A B
<tbody>
</tbody>
So as yo can see some are only there once while some like GA326 are there more because that site is made up of 4 blocks GA326/TR1 & 2 & 3 &4.
What I am trying to do is create vba code that will find all unit references (B) from a site ref (A) and return a msgbox for each giving me the ref and row number related to them, this is the code I am using so far and it almost works but not fully, it correctly finds the first unit ref but then keep putting that ref in the next msgbox,
If I type G0200 it works ok gives me one message box (A) x 1 as there is only one site ref in A and gives me correct unit ref GO200/GO
But if I put GA326 in it gives me 4 msgboxes which is right (A) X 4 but only gives me first Unit ref in (B) msgbox always has GA326/TR1 . not started doing the row part until I get the ref side working so heres the code I have so far Hope someone can help.
Private Sub TEST_Click()
Dim msgval As String
Dim refno As String
Application.Workbooks("yesdatav2.xlsm").Sheets("sites").Activate
msgval = InputBox("ENTER REF", "YES")
' Get the range of values
Dim rg As Range
Set rg = Sheet4.Range("A1:A1000")
' Create the dictionary
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
' Fill the dictionary
Dim cell As Range
For Each cell In rg
dict(cell.Value) = cell.Offset(0, 1).Value
Next
' Perform the Lookup
For Each cell In rg
If (cell.Value) = msgval Then
refno = Application.WorksheetFunction.VLookup(msgval, Sheet4.Range("SITES"), 2, False)
MsgBox refno
End If
Next
End Sub
Hope some one can help!!<strike></strike>
<strike></strike>
Thanks in advance fro taking the time to look at this much appreciated.
I have a worksheet called sites, each site has a ref no found in column A and be in this coloumn multiple times because each site also has a unit number found in column B the reason for this is some sites have multiple units or blocks,
A B
CO177 | CO177/CO |
CO179 | CO179/CO |
CY183 | CY183/CY |
DR186 | DR186/DR |
GA326 | GA326/TR1 |
GA326 | GA326/TR2 |
GA326 | GA326/TR3 |
GA326 | GA326/TR4 |
GO200 | GO200/GO |
GR202 | GR202/GR |
<tbody>
</tbody>
So as yo can see some are only there once while some like GA326 are there more because that site is made up of 4 blocks GA326/TR1 & 2 & 3 &4.
What I am trying to do is create vba code that will find all unit references (B) from a site ref (A) and return a msgbox for each giving me the ref and row number related to them, this is the code I am using so far and it almost works but not fully, it correctly finds the first unit ref but then keep putting that ref in the next msgbox,
If I type G0200 it works ok gives me one message box (A) x 1 as there is only one site ref in A and gives me correct unit ref GO200/GO
But if I put GA326 in it gives me 4 msgboxes which is right (A) X 4 but only gives me first Unit ref in (B) msgbox always has GA326/TR1 . not started doing the row part until I get the ref side working so heres the code I have so far Hope someone can help.
Private Sub TEST_Click()
Dim msgval As String
Dim refno As String
Application.Workbooks("yesdatav2.xlsm").Sheets("sites").Activate
msgval = InputBox("ENTER REF", "YES")
' Get the range of values
Dim rg As Range
Set rg = Sheet4.Range("A1:A1000")
' Create the dictionary
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
' Fill the dictionary
Dim cell As Range
For Each cell In rg
dict(cell.Value) = cell.Offset(0, 1).Value
Next
' Perform the Lookup
For Each cell In rg
If (cell.Value) = msgval Then
refno = Application.WorksheetFunction.VLookup(msgval, Sheet4.Range("SITES"), 2, False)
MsgBox refno
End If
Next
End Sub
Hope some one can help!!<strike></strike>
<strike></strike>