Changing Data Fetching In Established VBA Project

Quick background. I'm a SOLO IT Admin/Director for small business and my strength is administration, workstation, server support, virtualization and "gadgets." They want me to finally turn some much needed attention to our plethora of Excel spreadsheets that contain a lot of VBA, many of which has been in use for 10+ years. I'm game, but got some study ahead of me.

I've been tasked to change a way a sheet grabs data.

Sheet looks like this

I added the column W when I started to work on this project and will explain below...

Basically this sheet grabs data (job numbers) from another sheet according to a date range and calculates them with other subroutines and places those job numbers in the cells starting at C2. For the sake of this post, no need to worry about all those other cells because if I can just get this started, the rest will fall into place.

We want to eliminate the need to look at that other sheet based upon date range to get the job numbers and just have the macro (RUN REPORT, top left) grab the "jobs" listed in column W that the user will manually enter.

There are the two code blocks that are used to grab this data...

Sub IMPORT_ALL_INFORMATION()

'Set variables
Dim file_in As Long
Dim strInput As Variant
Dim i As Integer
Dim j As Integer
Dim l As Integer
Dim sTmp As String
Dim sJob As String
Dim sSchedPath As String

'end setting variables
Sheets("REPORT").Select
Range("C2").Select

sSchedPath = "C:Temp"
Call apiCopyFile("servernameApplicationsScheduleschedule-sschedule, S.xls", "C:Tempschedule, S.xls", 0)
dteStart = Application.Sheets("Report").Range("$G$27").Value
dteEnd = Application.Sheets("Report").Range("$J$27").Value
l = 4 ' First data row of schedule, S.xls
j = 2 ' First job row of Plate & Bar Spreadsheet
Do Until CDate(GetDate(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))) >= dteStart
l = l + 1
' Changed from 754 to 854...may be total jobs for year...went close to 800 jobs this year . BAW
If l = 854 Then
MsgBox ("Hello")
End If
sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
l = l + 1
End If
Loop
Do
sJob = ParseJob(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "B" & CStr(l)))
' Debug.Print sJob
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
On Error GoTo ErrorExit
Application.Sheets("report").Range("C" & CStr(j)).Value = vJobFolders(i)
j = j + 1
file_in = FreeFile 'file number
strFileToOpen = strpathtofile & vJobFolders(i) & strFilename
If Dir(strFileToOpen) <> "" Then
Open strFileToOpen For Input As #file_in
Put_Data_In_Array (file_in)
Organize_Array_For_Print
Close #file_in ' close the file
End If
ErrorExit:
Next i
l = l + 1
sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
l = l + 1
End If
Loop Until CDate(GetDate(sTmp)) >= dteEnd
Sheets("REPORT").Select

End Sub

Function GetValue(path, file, sheet, ref) As String
' Retrieves a value from a closed workbook
Dim arg As String
Dim pos As Integer
' Make sure the file exists
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
' Strip Any time from beginning of Date string
pos = InStr(GetValue, ":")
If pos <> 0 Then GetValue = Mid$(GetValue, pos + 3)
End Function

Just don't know how to tell it, with VBA, to take each job number in column W and do what it normally did the other way and then put a warning up if it doesn't match exactly and to STOP when it teaches a null cell.

Any suggestions on where to find some examples of this? If I can just get this part going, I am fairly confident I can do the rest. I can post the existing subroutines code if needed. I am thinking is is much easier than I think it will be. Just need the data from column W now and then it should feed into the rest of the arrays and produce the needed data.

And I've received tips on how I need to make this a database and all but I don't have this luxury right now. I just need this working by tomorrow.

Thanks so much!