VBA Hint Needed - Change Way An Existing Project Grabs Data

[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:

[dafyre]

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

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

You may have to resort to moving the Next rCell down a few lines at the time until you find the right spot... following OPC's can be tough if it's not nicely written... (OPC = other people's code).

[garak0410]

I've dusted off this project because, well, the time has truly come where they want the bulk of my time in development.

So, through some suggestions and research, I have created this part of the code:

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

Now, this works fine as long as there is data in Column W but once it is null or empty, it proceed to pull ALL jobs from the path/directory.

What I need it to do, is somewhere, tell it to stop once there is no more data (null/empty cell) in the Column W range...any suggestions?

[dafyre]

if rCell.value = "" or rCell.value is null then end ?

edit: Right above or below sJob = rCell.value ?