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:

Code:

---

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:

Code:

---

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.