VBA Hint Needed - Change Way An Existing Project Grabs Data

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 ?

garak0410

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

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

Let me give it a shot...

garak0410

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

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

Let me give it a shot...

Interesting...I now get a NEXT without a FOR error...on this line:
Next rCell
Sheets("REPORT").Select

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:

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

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

Let me give it a shot...

Interesting...I now get a NEXT without a FOR error...on this line:
Next rCell
Sheets("REPORT").Select

Make sure that if statement is all on one line? ... or change it from :

then end

to

then exit

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:

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

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

Let me give it a shot...

Interesting...I now get a NEXT without a FOR error...on this line:
Next rCell
Sheets("REPORT").Select

Make sure that if statement is all on one line? ... or change it from :

then end

to

then exit

Man, I feel I'm close here but funny how it leads to other errors...if I change to EXIT (rather then END), I get:

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:

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

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

Let me give it a shot...

Interesting...I now get a NEXT without a FOR error...on this line:
Next rCell
Sheets("REPORT").Select

Make sure that if statement is all on one line? ... or change it from :

then end

to

then exit

Man, I feel I'm close here but funny how it leads to other errors...if I change to EXIT (rather then END), I get:

Sorry... I haven't done much with VBA in a while... the semantics are different...
change "exit"

to "exit function"

garak0410

I am going to examine the code on the existing project that grabs this same data by date range and a list from another spreadsheet. Will see how it stops its data collection to complete the macro.

garak0410

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

I am going to examine the code on the existing project that grabs this same data by date range and a list from another spreadsheet. Will see how it stops its data collection to complete the macro.

It has a "Do Until" statement that keeps getting those job numbers until the date range is exceeded...wondering if I can DO UNTIL "null"?

garak0410

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

I am going to examine the code on the existing project that grabs this same data by date range and a list from another spreadsheet. Will see how it stops its data collection to complete the macro.

It has a "Do Until" statement that keeps getting those job numbers until the date range is exceeded...wondering if I can DO UNTIL "null"?

It looks like it needs a loop to do a "Do Until"...this sheet is full of loops...LOL