VBA Hint Needed - Change Way An Existing Project Grabs Data

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.

dafyre

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

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

Going from memory here, lol... it may barf errors all over his screen.

*passes a helmet to @scottalanmiller .

thwr

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

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

Now guess how much fun this is when you poke around with a C++ pointer in C# managed memory (or the other way around)

Hint: You'll get memory corruptions, ObjectDisposedExceptions and other funny things in places you would never imagine. And it's even more fun when running on mono.

garak0410

This may be be digressing some but I've spent some time this afternoon debugging and stepping through code...

The code never goes back to this block once done:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
Debug.Print rCell.Value:
sJob = rCell.Value

When it is done with Function FindJobDir

It returns to this line: vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
and it takes the next job listed in column W and then fires off Function FindJobDir

So, where exactly do we want to put this Do Until or the If ActiveCell.Value = vbNullString????

thwr

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

This may be be digressing some but I've spent some time this afternoon debugging and stepping through code...

The code never goes back to this block once done:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
Debug.Print rCell.Value:
sJob = rCell.Value

When it is done with Function FindJobDir

It returns to this line: vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
and it takes the next job listed in column W and then fires off Function FindJobDir

So, where exactly do we want to put this Do Until or the If ActiveCell.Value = vbNullString????

It's

Do Until rCell.Value = vbNullString
  'something
Loop

in your case. Or some other construct (while, do..until, do..while, for etc).

Hard to tell without seeing the actual source and the spreadsheet. Could you upload it filled with some testdata?