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
Bookmarks