VBA Hint Needed - Change Way An Existing Project Grabs Data

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.

garak0410

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

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.

Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.

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:

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.

Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.

Yeah. You need to read through the code and figure out where to put that Next rCell statement.

It is most likely near the end of the code... but look and see where it looks like everything is done, and put the Next statement there.

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:

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.

Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.

Yeah. You need to read through the code and figure out where to put that Next rCell statement.

It is most likely near the end of the code... but look and see where it looks like everything is done, and put the Next statement there.

Gotcha...call me nuts but this is kind of fun...learning a lot and trying not to get frustrated at this crazy code...

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:

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.

Question...if I comment out the Next rCell line to test this, then what do I do with the FOR line? It will tell me there is a FOR without a NEXT.

Yeah. You need to read through the code and figure out where to put that Next rCell statement.

It is most likely near the end of the code... but look and see where it looks like everything is done, and put the Next statement there.

Gotcha...call me nuts but this is kind of fun...learning a lot and trying not to get frustrated at this crazy code...

Welcome to the life of software development! lol. I actually enjoy it too.

garak0410

Yeah, this one will be a toughie because it won't even let me step through past a break-point with this error: