VBA Hint Needed - Change Way An Existing Project Grabs Data
-
@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 rCellAnd 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 StringsResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End FunctionIt 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:
-
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.
-
@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.
-
@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.
-
@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...
-
@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.
-
Yeah, this one will be a toughie because it won't even let me step through past a break-point with this error:
-
@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).
-
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.ValueIt 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 StringsResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End FunctionNow, 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?
-
if rCell.value = "" or rCell.value is null then end ?
edit: Right above or below sJob = rCell.value ?
-
@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 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 -
@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").SelectMake sure that if statement is all on one line? ... or change it from :
then end
to
then exit
-
@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").SelectMake 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:
-
@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").SelectMake 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"
-
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 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 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
-
@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"?
You mean like when the cell is empty?
-
@thwr 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:
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"?
You mean like when the cell is empty?
Yes...Do Until cell is empty...unless I am just thinking too hard on this...
