0

I have a large VBA project that I've been tasked with changing how it gathers some data.

Basically, the data will be entered in column W on the sheet and it's code in the Macro is this:

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

It grabs the data I want it to grab in column W.

Shortly after that, it kicks off a function that is in this block of code:

vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)

And that function, called FindJobDir, looks like this:

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

What is happening, after it grabs all the data it can find it column W, it just continues to add everything in the job path. It doesn't stop until all "jobs" in that path are found. I need it to stop doing that when data in column W is null/empty.

Not being a pro at VBA, don't know what to say or where...any suggestions?

Brian
  • 43
  • 6
  • You can limit the range by finding the last row with values in column W, see here: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba Or you can put an IF statement before the second line in the first block of code to skip that iteration if `rCell.Value = ""` – Scott Craner Jul 26 '16 at 18:30

2 Answers2

2

Instead of Exit Sub I would just use Exit For to break out of the for loop, in case you have code that runs after that for loop.

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

I also wouldn't hard code your range to 49 cells. If you input more or less data than that you will run into problems like you just did. The best solution would be to select the range you want to operate on first with something like

Dim reportSheet As Worksheet
Set reportSheet = Worksheets("REPORT")

Dim lastRow As Integer    
lastRow = reportSheet.Cells(reportSheet.Rows.Count, "W").End(xlUp).Row

Dim jobRange As Range
Set jobRange = reportSheet.Range("W2:W" & lastRow)

For Each rCell In jobRange
    Debug.Print rCell.Value ' colon is only needed for line breaks
    sJob = rCell.Value
    ' ...
Next

See this answer for an explanation on how the last row is calculated.

cheezsteak
  • 2,731
  • 4
  • 26
  • 41
  • It's been crazy at work being SOLO IT guy for "all things IT" but I forgot to comment on how this worked for me and I am so appreciative of it. – Brian Aug 29 '16 at 20:33
  • One last question...how can I force it to be EXACTLY what they type on Column W., using the above code? They are running into a problem where they enter a job say 161616 and if that job has phases, which we name like 161616A, 161616B, etc. it will pull in all associated jobs starting with 161616. We need it to be an exact match. – Brian Aug 29 '16 at 20:36
  • The only place where I possibly see being the cause is `strPath & "*"`. Depending on what your directory pattern is, `strPath & "\*"` might solve it but that's more focused on your specific task than the actual question. – cheezsteak Aug 30 '16 at 02:57
  • Yeah, I tried the "\*" and it went wacko and brought in a ton of unrelated stuff... – Brian Sep 06 '16 at 12:55
  • I am thinking it is in here... For Each rCell In Worksheets("REPORT").Range("W2:W50") If IsEmpty(rCell.Value) Then Exit Sub Debug.Print rCell.Value: sJob = rCell.Value I think something to make sure rCell.Value is exact is needed here. – Brian Sep 06 '16 at 13:14
  • Can I use xlWhole somewhere in here? – Brian Sep 06 '16 at 13:55
  • @Brian this is starting to sound like a separate question. – cheezsteak Sep 09 '16 at 16:23
1

If I'm understanding correctly you want to stop the code running if the value in column W is blank. If so the following edit should work for you...

Change this:

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

to this:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
  sJob = rCell.Value
tjb1
  • 747
  • 9
  • 30