VBA Hint Needed - Change Way An Existing Project Grabs Data

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

thwr

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

garak0410

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

thwr

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

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

If ActiveCell.Value = vbNullString

ActiveCell is not very good, because you need to move the focus around which causes much CPU load. Just test for vbNullString, that's a predefined constant.

http://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba

garak0410

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

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

If ActiveCell.Value = vbNullString
http://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba

Great...will see what happens...

thwr

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

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

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

If ActiveCell.Value = vbNullString
http://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba

Great...will see what happens...

Don't blame me in case of a nuclear meltdown

garak0410

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

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

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

If ActiveCell.Value = vbNullString
http://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba

Great...will see what happens...

Don't blame me in case of a nuclear meltdown

Never...just jump into a lead lined Fridge before I begin...

dafyre

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

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

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

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

If ActiveCell.Value = vbNullString
http://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba

Great...will see what happens...

Don't blame me in case of a nuclear meltdown

Never...just jump into a lead lined Fridge before I begin...

*holds door open for every one to enter... passes out helmets to everyone as they pass by.

dafyre

Do Until would work fine, but you'd just have to remember to increment the row number each time...

cellColumn="W"
cellRow=1
do 
  rCell=Sheet1.Cell(cellColumn+cellRow)
  rem do other stuff here over many many lines
  rem keep doing stuff until it's done...
  cellRow=cellRow+1
until (rCell.value="" or rCell.value is null)

scottalanmiller

Man I hate that "do... until()" syntax.