VBA Hint Needed - Change Way An Existing Project Grabs Data

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?

garak0410

Update on this project...a block of code like this did the trick:

Dim reportSheet As Worksheet
Set reportSheet = Worksheets("REPORT")

Dim lastRow As Integer
lastRow = reportSheet.Cells(reportSheet.Rows.Count, "W").End(xlUp).Row

Dim jobRange As Range
Set jobRange = reportSheet.Range("W2:W" & lastRow)

For Each rCell In jobRange
Debug.Print rCell.Value ' colon is only needed for line breaks
sJob = rCell.Value
' ...
Next

Now, of course, they are asking for this to go a step further. They want what is typed in Column W to be an exact match to what it goes to look and find at the file location path.

For example., there may be a job called 161616 and it may have several phases like A, B, C etc. So if say he is ordering for 161616 (no phase), it will bring in 161616 and also any A, B, or C phase that is out there. If he specifically types in 161616A, it will only pull that one.

Is there a way to make it look for an exact match?

garak0410

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

Update on this project...a block of code like this did the trick:

Dim reportSheet As Worksheet
Set reportSheet = Worksheets("REPORT")

Dim lastRow As Integer
lastRow = reportSheet.Cells(reportSheet.Rows.Count, "W").End(xlUp).Row

Dim jobRange As Range
Set jobRange = reportSheet.Range("W2:W" & lastRow)

For Each rCell In jobRange
Debug.Print rCell.Value ' colon is only needed for line breaks
sJob = rCell.Value
' ...
Next

Now, of course, they are asking for this to go a step further. They want what is typed in Column W to be an exact match to what it goes to look and find at the file location path.

For example., there may be a job called 161616 and it may have several phases like A, B, C etc. So if say he is ordering for 161616 (no phase), it will bring in 161616 and also any A, B, or C phase that is out there. If he specifically types in 161616A, it will only pull that one.

Is there a way to make it look for an exact match?

This isn't a case where I use the xlWhole command, is it???

dafyre

Are they Phases always Letters? ie: A,B,C...Z.... or are there numbers too?

I'm trying to think as to whether or not there's a LIKE operator in VBA... It's been so long I can't remember...

You might could modify the sJob....

sJob=rCell.value + "*"

And check that the routines that look for the paths are prepared for multiple targets... ie:

The user enters Job 161616 and it has Phases A,B, and C... each phase would need to be processed... That complicates things a bit though, depending on how you are looking at the folder names. Can you post that bit of code?