Problems with extracting data from worksheet

[Mikeymike_W]

Hi and thanks in advance for your help,

I have the following code which is meant to get two different pieces of data from two different worksheets in the same workbook:

Private Sub CBanswer_Click()

Dim s As Worksheet, myValue As String, f As Range
Dim Q As Worksheet
Dim myvalue2 As String
Dim p As Range


    Set s = Worksheets("Lists")
    myValue = Comboxmajor.Value
    Set f = s.Range("K1:K500").Find(myValue, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        TBhistory.Value = f.Offset(0, 1).Value
    Else
        MsgBox "Does not exists : " & myValue
    End If




Set Q = Worksheets("Interpretations")
    myvalue2 = Comboxmajor.Value & " " & Comboxarea.Value
    Set p = Q.Range("A:A").Find(myvalue2, LookIn:=xlValues, lookat:=xlWhole)
    If Not p Is Nothing Then
        TBanswer.Value = p.Offset(0, 1).Value
    Else
        MsgBox "Does not exists : " & myvalue2
    End If


End Sub

It seems to work fine for TBhistory but it only gets data from column A on the "Interpretations" worksheet.

I've tripple checked the value in the Interpretations worksheet to make sure they are right.

Many thanks in advance for any help,

Mike

 

[JoeMo]

Hi and thanks in advance for your help,

I have the following code which is meant to get two different pieces of data from two different worksheets in the same workbook:

Private Sub CBanswer_Click()

Dim s As Worksheet, myValue As String, f As Range
Dim Q As Worksheet
Dim myvalue2 As String
Dim p As Range


    Set s = Worksheets("Lists")
    myValue = Comboxmajor.Value
    Set f = s.Range("K1:K500").Find(myValue, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        TBhistory.Value = f.Offset(0, 1).Value
    Else
        MsgBox "Does not exists : " & myValue
    End If




Set Q = Worksheets("Interpretations")
    myvalue2 = Comboxmajor.Value & " " & Comboxarea.Value
    Set p = [COLOR=#ff0000][B]Q.Range("A:A")[/B][/COLOR].Find(myvalue2, LookIn:=xlValues, lookat:=xlWhole)
    If Not p Is Nothing Then
        TBanswer.Value = p.Offset(0, 1).Value
    Else
        MsgBox "Does not exists : " & myvalue2
    End If


End Sub

It seems to work fine for TBhistory but it only gets data from column A on the "Interpretations" worksheet.

I've tripple checked the value in the Interpretations worksheet to make sure they are right.

Many thanks in advance for any help,

Mike

The bit in bold red font is limiting the search to col A. Change it to whatever range you want to search for myvalue2.

 

[Mikeymike_W]

Joe thanks very much, I though A:A wouldve been for the whole worksheet.
Thanks for pointing out the mistake, it works great now

 

[JoeMo]

Joe thanks very much, I though A:A wouldve been for the whole worksheet.
Thanks for pointing out the mistake, it works great now

You are welcome - thanks for the reply.