[rsdjimbie]

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.

[rsdjimbie]

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.

[JBeaucaire]

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.

[rsdjimbie]

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

[rsdjimbie]

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

[rsdjimbie]

Without merging.

[JBeaucaire]

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

[rsdjimbie]

Very clever! Thanks JB.