I want to lookup a value and return it's position or lacation within a area it is falling under. Please refer to attached file for further clarification.

I want to lookup a value and return it's position or lacation within a area it is falling under. Please refer to attached file for further clarification.

You should get into the habit of editing all the "selecting" out of your macros. The recorder shows "selections" because it is recording your human activities. But you can merge 2-10 rows of human steps into a single direct command. Like so:


Sub UpdatePlanningSheet()
'
' UpdatePlanningSheet Macro
' Macro recorded 26/06/2007 by Louise
'
' Keyboard Shortcut: Ctrl+p
'

Application.ScreenUpdating = False
Range("V2:W2").Copy Range("V3:W51")
Range("V53:W53").Copy Range("V54:W100")
Range("V102:W102").Copy Range("V103:W155")
Application.ScreenUpdating = True

Range("B12").Select
MsgBox "Updated!"
End Sub

Another:

Sub EmailReady()
'
' EmailReady Macro
' Macro recorded 13/11/2007 by Renier Struwig
'
' Keyboard Shortcut: Ctrl+e
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Emb").Select
ActiveSheet.Unprotect
Cells.Value = Cells.Value
Columns("L:M").Delete xlShiftToLeft
Range("A1").Select

Sheets("Prod A").Select
ActiveSheet.Unprotect
Columns("Y:Y").Delete Shift:=xlToLeft
ActiveSheet.Shapes("Button 130").Cut
ActiveSheet.Shapes("Button 132").Cut
ActiveSheet.Shapes("Button 131").Cut
Range("H1:S2").Value = Range("H1:S2").Value
Range("E4").Value = Range("E4").Value

With Rows("5:100")
.Orientation = 0
.AddIndent = False
.ReadingOrder = xlContext
.MergeCells = False
.FormatConditions.Delete
.Value = .Value
End With

Range("A5").Select

Sheets("Prod B").Select
ActiveSheet.Unprotect
Columns("Y:Y").Delete Shift:=xlToLeft

Range("H1:S2").Value = Range("H1:S2").Value
Range("E4").Value = Range("E4").Value

With Rows("5:100")
.Orientation = 0
.AddIndent = False
.ReadingOrder = xlContext
.MergeCells = False
.FormatConditions.Delete
.Value = Value
End With
Range("A5").Select

Sheets("Prod C").Select
ActiveSheet.Unprotect
Columns("Y:Y").Delete Shift:=xlToLeft
Range("H1:S2").Value = Range("H1:S2").Value
Range("E4").Value = Range("E4").Value

With Rows("5:100")
.Orientation = 0
.AddIndent = False
.ReadingOrder = xlContext
.MergeCells = False
.FormatConditions.Delete
.Value = .Value
End With
Range("A5").Select

Sheets("Prod D").Select
ActiveSheet.Unprotect
Columns("Y:Y").Delete Shift:=xlToLeft

Range("H1:S2").Value = Range("H1:S2").Value
Range("E4").Value = Range("E4").Value
With Rows("5:100")
.Orientation = 0
.AddIndent = False
.ReadingOrder = xlContext
.MergeCells = False
.FormatConditions.Delete
.Value = .Value
End With
Range("A5").Select

Sheets(Array("SPPA", "Invoiced", "HO plan", "Schedule", "#No", _
"Planning", "Management", "SM+WE")).Select
Sheets("SM+WE").Activate
ActiveWindow.SelectedSheets.Delete

Sheets("Emb").Select

'To save on any desktop and as cell content
Dim fName As String, DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
fName = Range("H1").Text & "Prod.Progress"
ActiveWorkbook.SaveAs DTAddress & fName & ".xls"

MsgBox "Ready to email!"
End Sub

Compare those to the original and you'll see large efficiency increases and much shorter code to maintain.

=======
I have to admit. Your sheet layout and your desired result is stumping me for a worksheet formula... still thinking about it.

NOTE: Merged cells are a real pain to work around.

Thank you for the additional help, I am also still chewing on this one, I am sure we will get it though.

Wow, the amended macros move fast! Thank you JB.

Without merging.

Maybe like this... I also spent some more time condensing some of your other macros. Hopefully they still work as intended... hehe.

Very clever! Thanks JB.