Using FIND on 2 linked ranges

[JamesW]

Hi guys,

I have a userform that contains 2 comboboxes (com_EM and com_Month). What I want to do is for the user to choose a country from com_EM, and then a month from com_month (easy). Once this has been done search the data and find the comments which are linked to this EM/Month combination.

E.g.

Row 1 contains the end markets.
Row 2 contains the months.

Denmark would repeat from Jan - Dec (C1:N1), and then the next end market will repeat from Jan - Dec (O1:Z1) and so on.

I am trying to use Find to find the end market (which I've done) but then I want it to search for the month relating to that end market.

E.g.

User selects Denmark (found in cell C1) and Jan (found in cell C2) so first search for Denmark, then find Jan and return the comments below.

User selects U.K. (found in cell FC1) and Jan (found in FC2) - This is where it goes wrong, as it finds the Jan in cell C2 first and returns that.

Here is what I have done so far:

Option Explicit
Dim foundEM, foundMn As Integer
Dim emFind, monthFind As Range

Private Sub com_EM_Change()
    If com_Month.Value = "" Then
        Exit Sub
    Else
        populateCom
    End If
End Sub
Private Sub com_Month_Change()
    If com_EM.Value = "" Then
        Exit Sub
    Else
        populateCom
    End If
End Sub

Sub populateCom()
    Set emFind = Range("C1:GX1").find(com_EM)
    Set monthFind = Range("C1:GX2").find(com_Month, emFind)
    
    If Not emFind Is Nothing Then
        foundEM = emFind.Column
    End If
    
    If Not monthFind Is Nothing Then
        foundMn = monthFind.Column
    End If
    
    txt_Reliability = Cells(3, foundMn)
    txt_Quality = Cells(4, foundMn)
    txt_NPI = Cells(5, foundMn)
    txt_Projects = Cells(6, foundMn)
End Sub

Any help would be greatly appreciated.

Cheers,

James

 

[JamesW]

Bump.

 

[JamesW]

Bump.

 

[GlennUK]

Regarding your line of code:

Set monthFind = Range("C1:GX2").find(com_Month, emFind)

... shouldn't that be:

    Set monthfind = Range("C1:GX2").Find(com_Month, emfind, , , xlByColumns)

 

[Richard Schollar]

Hi James

Base the second search (for the month) off of the first:

Dim r1 as Range, r2 As Range
 
Set r1 = Rows(1).Find(What:="YourCountryHere")
 
Set r2 = r1.Offset(1).Resize(,12).Find(What:="YourMonthHere")  'offset to the date row and resize to take up to 12 months

Make sense?

 

[JamesW]

Hi James

Base the second search (for the month) off of the first:

Dim r1 as Range, r2 As Range
 
Set r1 = Rows(1).Find(What:="YourCountryHere")
 
Set r2 = r1.Offset(1).Resize(,12).Find(What:="YourMonthHere")  'offset to the date row and resize to take up to 12 months

Make sense?

I've never looked into .Resize, so it doesn't make much sense I'll take a look on Google.

Glenn, works fine thanks.

One more question, If I now want to do another search but this time the user chooses the 'Area' (which is by Row, not column) instead of 'End Market' how would I do this?

 

[GlennUK]

What exactly is 'Area'? It is not mentioned anywhere in this thread.