Ask Experts Questions for FREE Help !
Ask
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #1

    Feb 8, 2010, 12:01 PM
    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.
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #2

    Feb 8, 2010, 12:04 PM
    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.
    Attached Files
  1. File Type: zip Lookup.zip (59.0 KB, 45 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Feb 8, 2010, 01:38 PM

    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.
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #4

    Feb 8, 2010, 11:02 PM

    Thank you for the additional help, I am also still chewing on this one, I am sure we will get it though.
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #5

    Feb 8, 2010, 11:26 PM
    Wow, the amended macros move fast! Thank you JB.
    rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #6

    Feb 18, 2010, 08:30 AM
    Without merging.
    Attached Files
  3. File Type: xls Lookup.xls (395.5 KB, 187 views)
  4. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #7

    Feb 18, 2010, 12:11 PM

    Maybe like this... I also spent some more time condensing some of your other macros. Hopefully they still work as intended... hehe.
    Attached Files
  5. File Type: zip Lookup.zip (54.3 KB, 44 views)
  6. rsdjimbie's Avatar
    rsdjimbie Posts: 96, Reputation: 3
    Junior Member
     
    #8

    Feb 20, 2010, 10:41 AM

    Very clever! Thanks JB.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Lookup key for explorer [ 1 Answers ]

I cannot find the requested lookup key in any active activation context for my internet explorer 8

Lookup Key [ 1 Answers ]

Lookup key was not found in ay active activitation content

Maiden Name Lookup [ 3 Answers ]

I am working on finding classmates for a 50th year reunion. Can someone tell me the best way to find females when all I have is their maiden name.

Forward lookup,reverse lookup [ 1 Answers ]

Hi I am not getting the idea what exactly is FORWARD LOOKUP, REVERSE LOOKUP,zones while configuring DNS . Can anone explain briefly or give me the link to go through:)

Cascading lookup fields... [ 3 Answers ]

Want to use the form frmCardDetails to populate tblCards. Want to select the sport from a lookup field linked to tblSports and then the League from tblLeagues, BUT only displaying the leagues that are included in that sport. I have looked at http://support.microsoft.com/kb/209595/en-us and...


View more questions Search