Code required - a bit tricky I think :)

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
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 :)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

I hope this gets you started.
PGC

Code:
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
 

Forum statistics

Threads
1,136,261
Messages
5,674,704
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top