VBA Hint Needed - Change Way An Existing Project Grabs Data

garak0410

I know it is difficult to seek development help but perhaps a little boost in the right direction could help.

We have a spreadsheet that 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.

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.

Thanks so much!

dafyre

How about something like this?... Keep in mind, you'll need to code around blank boxes, etc...

Dim myRange As Range

Set myRange = Sheet1.Range("W1", "W5")

For Each ritem In myRange
 msgbox (ritem.Value)
 
Next

In the Code Above, Sheet1 is the name of the worksheet. If the Sheet has a space in its name, you may have to get the Sheet1 object a different way.

JaredBusch

yeah, you need to look at the existing code and replace the bit that looks at the other sheet with something like what @dafyre gave you.

garak0410

Here's the original Subroutine and Function it originally ran...as stated, want to rid the method of going to another sheet to pull job numbers by date and rather get them from column W and then it will continue to do the same arrays and calculations as it did before...

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("\servername\Applications\Schedule\schedule-s\schedule, S.xls", "C:\Temp\schedule, 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

thwr

Easier to read:

Sub IMPORT_ALL_INFORMATION()
    REM 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

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

    sSchedPath = "C:\Temp"
    Call apiCopyFile("\servername\Applications\Schedule\schedule-s\schedule, S.xls", "C:\Temp\schedule, S.xls", 0)
    dteStart = Application.Sheets("Report").Range("$G$27").Value
    dteEnd = Application.Sheets("Report").Range("$J$27").Value
    
    REM    First data row of schedule, S.xls
    l = 4 
    
    REM    First job row of Plate & Bar Spreadsheet
    j = 2 

    Do Until CDate(GetDate(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))) >= dteStart
        l = l + 1
        
        REM    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)))
        REM    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
            
            REM file number
            file_in = FreeFile 
            
            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 REM    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
    REM    Retrieves a value from a closed workbook
    Dim arg As String
    Dim pos As Integer
    
    REM    Make sure the file exists
    If Right(path, 1) <> "" Then 
        path = path & ""
    End If
    
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    
    REM    Create the argument
    arg = "REM " & path & "[" & file & "]" & sheet & "REM !" & _
    Range(ref).Range("A1").Address(, , xlR1C1)
    REM    Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
    REM    Strip Any time from beginning of Date string
    pos = InStr(GetValue, ":")
    If pos <> 0 Then GetValue = Mid$(GetValue, pos + 3)
End Function

thwr

@garak0410 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.

Little helper function

Function IsNullOrEmpty(val as String) as Boolean
  REM Strings are always initialized as "" in VB/VBA/VBS, so checking for Null is redundant. At least In theory.
  If (val is Null or val = "") Then
    IsNullOrEmpty = True
  Else
    IsNullOrEmpty = False
  End If
End Function

To process your values (like @dafyre suggested):

Dim myRange As Range

Set myRange = Sheet1.Range("W1", "W5")

For Each ritem In myRange
  If (IsNullOrEmpty(ritem.Value)) Then 
    REM Empty string, processing finished
    Exit For
  Else
    REM Do something here with ritem.Value
  End If
Next

The original script is a bit... ugly. Could be easier to have the XLS with test data only.

garak0410

Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

sJob = Report.Range("W2:W50").Select(CStr(l))

But alas, it doesn't work...

garak0410

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

sJob = Report.Range("W2:W50").Select(CStr(l))

But alas, it doesn't work...

I am going to play around with variations of this but each time I change something, new errors (mostly syntax) pop up...it is maddening.

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

sJob = Report.Range("W2:W50").Select(CStr(l))

But alas, it doesn't work...

I am going to play around with variations of this but each time I change something, new errors (mostly syntax) pop up...it is maddening.

Welcome to the world of software devleopment, lol.

@thwr is right. I'd take a little while to clean up the code and make it more readable (see his code post)... and then go back to make your changes.

Are all of these jobs separated out into their own Excel files (or are they some other type of file that we don't care about?)

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Maybe I am just thinking a little simplistic, but was thinking that this could be all I need, after commenting out the code that goes to the other sheet:

sJob = Report.Range("W2:W50").Select(CStr(l))

But alas, it doesn't work...

I am going to play around with variations of this but each time I change something, new errors (mostly syntax) pop up...it is maddening.

Welcome to the world of software devleopment, lol.

@thwr is right. I'd take a little while to clean up the code and make it more readable (see his code post)... and then go back to make your changes.

Are all of these jobs separated out into their own Excel files (or are they some other type of file that we don't care about?)

The "jobs" are metal building jobs that are in folders on a network share. The folders the "job numbers." When searching for these jobs, it goes out to these job folders, finds a file called o PltSum.out and then takes that data and calculates the needed materials to order for the week. That calculation isn't my focus as that should still work if I get this to work differently.

garak0410

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

A little formatting my help you see the problem...

For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
Next rcell

sJob = rcell.Value

It looks like you're trying to assing sJob after you come out of the For Each loop... Try:

For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
 sJob = rcell.Value

Next rcell

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

A little formatting my help you see the problem...
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
Next rcell

sJob = rcell.Value
It looks like you're trying to assing sJob after you come out of the For Each loop... Try:
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
 sJob = rcell.Value

Next rcell

OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

A little formatting my help you see the problem...
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
Next rcell

sJob = rcell.Value
It looks like you're trying to assing sJob after you come out of the For Each loop... Try:
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
 sJob = rcell.Value

Next rcell
OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))

sJob=ParaseJob(rcell.value) ?

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

A little formatting my help you see the problem...
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
Next rcell

sJob = rcell.Value
It looks like you're trying to assing sJob after you come out of the For Each loop... Try:
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
 sJob = rcell.Value

Next rcell
OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))
sJob=ParaseJob(rcell.value) ?

The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

A little formatting my help you see the problem...
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
Next rcell

sJob = rcell.Value
It looks like you're trying to assing sJob after you come out of the For Each loop... Try:
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
 sJob = rcell.Value

Next rcell
OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))
sJob=ParaseJob(rcell.value) ?
The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.

puts on hard hat...

Here... Hold my beer?

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

A little formatting my help you see the problem...
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
Next rcell

sJob = rcell.Value
It looks like you're trying to assing sJob after you come out of the For Each loop... Try:
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
 sJob = rcell.Value

Next rcell
OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))
sJob=ParaseJob(rcell.value) ?
The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.
puts on hard hat...

Here... Hold my beer?

Funny...it is amazing what a closed door and no distractions can do to your focus and clarity...and maybe "beer."...

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

In my weeks long struggle with this, I did at least try somethiing...I commented out all of the lines that grabbed the data from the other sheet (see code in posts above) and made a varient called rcell and tried this line of code:

For Each rcell In Worksheets("Sheet1").Range("W2:W50"): Debug.Print rcell.Value: Next rcell
sJob = rcell.Value

I keep getting SUBSCRIPT OUT OF RANGE.

I guess this is a lack of my own education mixed with bad coding in the first place...

A little formatting my help you see the problem...
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
Next rcell

sJob = rcell.Value
It looks like you're trying to assing sJob after you come out of the For Each loop... Try:
For Each rcell In Worksheets("Sheet1").Range("W2:W50")
 Debug.Print rcell.Value: 
 sJob = rcell.Value

Next rcell
OK...I am giving this a shot...I am at the point in the old code where I see when SJOB is equal to the value of the job on the date range on the other spreadsheet. Since I am moving these jobs to manually entered job numbers in Column W, will se how this works. Curretly, SJOB is equal to: sJob = ParseJob(GetValue(sSchedPath, "Schedule S.xls", "LOG (2)", "B" & CStr(l)))
sJob=ParaseJob(rcell.value) ?
The more I work on this (uninterrupted from day to day issues that I am NOT getting today, wooo!), the more I think I am "getting it."...I don't think we need ParseJob because what that did before was remove spaces in the job numbers that were lisited on Schedule, S. So I just need it to be rcell.value...about to code and see what happens.
puts on hard hat...

Here... Hold my beer?
Funny...it is amazing what a closed door and no distractions can do to your focus and clarity...and maybe "beer."...

Is debug.print rCell.value showing anything in the immediate window (CTRL G or View -> Immediate window) ?

garak0410

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

I'm making progress even if still getting errors...

I did put job numbers in rows W2 and W3. SJOB is still a null value even after this:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
Debug.Print rCell.Value:
sJob = rCell.Value
Next rCell

And some how, it is still pulling data from Schedule, S and putting it in Column C...I am trying to track that down...

While debugging, it DOES pull from column W and I see SJOB being assigned a job number both by hovering over the variable and in the intermediate window. It then does the same for the next job number (only entered two for this example, cell W2 and W3. The intermediate Window does show both jobs:

But it is not holding the SJOB data as it eventually goes null...

When it gets to this part:

Function FindJobDir(ByVal strPath As String) As String
Dim sResult As String

sResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End Function

It just starts grabbing all job folder information from the server location...luckily it fails when it hits a ZIP folder otherwise it would have been a data overrun...

When it is executing this line: vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

SJOB Shows Null like this:

dafyre

That is right. The way the code works, is it just prints the rcell.value to the screen, sets SJOB =rcell.value, and then at the Next rCell line, it goes to the next cell. It's not actually doing anything beneath that.

The trick will be to comment out the "Next rCell" line

and find out where the end of the process is, and then on the next line add next rCell and see if that works.

If this thing is actively modifying files and such, I'd hightly recommend backing everything up before running it while you are working on that part.