Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Lookup (https://www.askmehelpdesk.com/showthread.php?t=444612)

  • Feb 8, 2010, 12:01 PM
    rsdjimbie
    lookup
    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.
  • Feb 8, 2010, 12:04 PM
    rsdjimbie
    1 Attachment(s)
    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.
  • Feb 8, 2010, 01:38 PM
    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:

    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.
  • Feb 8, 2010, 11:02 PM
    rsdjimbie

    Thank you for the additional help, I am also still chewing on this one, I am sure we will get it though.
  • Feb 8, 2010, 11:26 PM
    rsdjimbie
    Wow, the amended macros move fast! Thank you JB.
  • Feb 18, 2010, 08:30 AM
    rsdjimbie
    1 Attachment(s)
    Without merging.
  • Feb 18, 2010, 12:11 PM
    JBeaucaire
    1 Attachment(s)

    Maybe like this... I also spent some more time condensing some of your other macros. Hopefully they still work as intended... hehe.
  • Feb 20, 2010, 10:41 AM
    rsdjimbie

    Very clever! Thanks JB.

  • All times are GMT -7. The time now is 10:59 PM.