Code required - a bit tricky I think :)

[The Idea Dude]

Hi all,

I am looking for some code that I can't figure out...this is what I have and require:

I have a sheet called "Load Config". On the sheet there is 4 cells of importance. E2, M2, P7 and R7

I have another sheet called "Performance Data". This is where it gets a little bit tricky

I have 4 'tables' set up on this sheet (Performance Data).

A3:O13 is one table (table 1)
A17:O27 is another table (table 2)
Q3:AE13 is another table (table 3)
Q17:AE27 is another table (table 4)

I require code that will look at the correct table and then go to the correct row and column of that table and then copy the value in that cell to Range("D31") on the Performance Data sheet.

This is how it will work: (mix of code and normal language )

If sheets("Load Config").range("E2")="no" then either table 1 or table 3 will be used depending on other criteria.

If sheets("Load Config").range("E2")<>"no" then either table 2 or table 4 will be used depending on other criteria.

If sheets("Load Config").range("M2")<> 1 then use table 1 or 2 depending on other previous criteria, else use table 3 or 4 depending on previous criteria.

NOW WE KNOW WHICH TABLE TO LOOK AT

P7 of Sheets("Load Config") is the value we are looking for in the first row of the table.

R7 of Sheets("Load Config") is the value we are looking for in the first column of the table.

the value of the cell at the intersection of the resultant row and column is the value that should be copied and pasted to Range("D31") on the Performance Data sheet.

I hope that all makes some sense and I appreciate peoples effort to help me with this.

Thank You

 

[pgc01]

Hi

I hope this gets you started.
PGC

Sub GetValue()
Dim rR As Range, rC As Range, vE2, vM2, lCol As Long, lRow As Long

Sheets("Performance Data").Activate
vE2 = Sheets("Load Config").Range("E2").Value
vM2 = Sheets("Load Config").Range("M2").Value

' Determine the lookup table
If vE2 = "no" And vM2 <> 1 Then
    Set rR = ActiveSheet.Range("A3:O13")
ElseIf vE2 = "no" And vM2 = 1 Then
    Set rR = ActiveSheet.Range("Q3:AE13")
ElseIf vE2 <> "no" And vM2 <> 1 Then
    Set rR = ActiveSheet.Range("A17:O27")
Else
    Set rR = ActiveSheet.Range("Q17:AE27")
End If

' Get the column
Set rC = rR.Rows(1).Find(What:=Sheets("Load Config").Range("P7").Value, LookIn:=xlValues, _
    lookat:=xlWhole)

If rC Is Nothing Then
    MsgBox "Value " & Sheets("Load Config").Range("P7").Value & _
                           "not found in row " & rC.Rows(1).Address
    Exit Sub
End If

lCol = rC.Column

' Get the row
Set rC = rR.Columns(1).Find(What:=Sheets("Load Config").Range("R7").Value, LookIn:=xlValues, _
    lookat:=xlWhole)

If rC Is Nothing Then
    MsgBox "Value " & Sheets("Load Config").Range("R7").Value & _
                           "not found in column " & rC.Columns(1).Address
    Exit Sub
End If

lRow = rC.Row

' Write the result
Range("D31").Value = ActiveSheet.Cells(lRow, lCol)

End Sub

 

[The Idea Dude]

Works a treat......thank you very much for your efforts