Results 1 to 16 of 16

Select case with public variable???

Threaded View

  1. #1
    Registered User
    Join Date
    10-27-2011
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Select case with public variable???

    I am trying to write a code that will look at the values in several worksheets and if the cell it looks at has the same value as the job number input by the user, I want to copy and paste the row into a worksheet called "Tracking Macro". I had the program running but the screen flashes as it runs so I decided to try altering it and use the screen updating = false line. However that meant I had to take out all the selection and active lines I used. Now the sub lookup will not run. Here is the start of the code...

    Public StLook As Range
    Public LookSheet As String
    Public JobNum As String
    Public PrtCell As Range
    Public EndPrt As String
    
    Sub output()
    
    Dim ThisYr As String
    
    On Error GoTo ErrHandler
    
    Worksheets("Tracking Macro").Cells.Clear
    
    ScreenUpdating = False
    
        ThisYr = Worksheets("BNG Trends").Range("B1").Value
        JobNum = InputBox("Please provide the BNG job number you wish to view:", "Job Number")
        
         Worksheets("Tracking Macro").Range("A4", "M4").Value = Worksheets(ThisYr & " Invoice Log").Range("A6", "M6").Value
        
            Set PrtCell = Worksheets("Tracking Macro").Range("A4")
            EndPrt = "M"
            Call format
            LookSheet = ThisYr & " Invoice Log"
            Set StLook = Range("A7")
            Call lookup
    That all runs ok, then I call the "lookup" sub and I get error message 438: "Object does not support this property or method." After some process of ellimination I believe it is that the computer is not recignizing "StLook" as a range. Help ! I am fairly new to VBA programming. Here is the "lookup" sub...

    Sub lookup()
    
    Do While Worksheets(LookSheet).StLook.Value <> ""
    
        Select Case Worksheets(LookSheet).Range(StLook).Value
        Case JobNum
                Do Until PrtCell.Value = ""
                    Set PrtCell = PrtCell.Offset(1, 0)
                Loop
                
            Worksheets("Tracking Macro").Range("A" & PrtCell.Row, EndPrt & PrtCell.Row).Value = Worksheets(LookSheet).Range("A" & StLook.Row, EndPrt & StLook.Row).Value
            Call format2
            
            Set StLook = StLook.Offset(1, 0)
        Case Else
            Set StLook = StLook.Offset(1, 0)
        End Select
        
    Loop
    
    End Sub
    Last edited by Kelso89; 10-27-2011 at 11:40 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1