VBA Hint Needed - Change Way An Existing Project Grabs Data

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?

garak0410

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

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?

Good timing...I just went back to sJob=rCell.value and was thinking it could be there. They can be letters or numbers and might be dashes sometimes.

The complex problem with this sheet is when he goes to order his steel (i.e. this sheet), he may only need phase A or just B...and when he enters that, it pulls in just that. But a good example right now is this...job 161343 also has a job called 161343_PORTAL. If he enters just 161343, that's all he needs for that week. But it still pulls _PORTAL. Same thing happens if he enters a job with phases but needs the one with no A, B, C, etc. It will still pull A, B, C. etc. and he doesn't need it .

So it is in this block:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value

That proceeds to this line vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

And the FindJobDir subroutine is 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

UPDATE: Interesting...I did you recommended sJob=rCell.value + "*" ...putting in a job that has phases but with no phase needed for that week, brings up type mismatch error...if I put in a phased job, it completes successfully.

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:
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?
Good timing...I just went back to sJob=rCell.value and was thinking it could be there. They can be letters or numbers and might be dashes sometimes.

The complex problem with this sheet is when he goes to order his steel (i.e. this sheet), he may only need phase A or just B...and when he enters that, it pulls in just that. But a good example right now is this...job 161343 also has a job called 161343_PORTAL. If he enters just 161343, that's all he needs for that week. But it still pulls _PORTAL. Same thing happens if he enters a job with phases but needs the one with no A, B, C, etc. It will still pull A, B, C. etc. and he doesn't need it .

So it is in this block:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value

That proceeds to this line vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

And the FindJobDir subroutine is 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

UPDATE: Interesting...I did you recommended sJob=rCell.value + "*" ...putting in a job that has phases but with no phase needed for that week, brings up type mismatch error...if I put in a phased job, it completes successfully.

You could do a try...catch block or an onerror statement to catch that error... so if it bombs with the type mismatch, then you can force it to do try it for a non-phased job?

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:
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?
Good timing...I just went back to sJob=rCell.value and was thinking it could be there. They can be letters or numbers and might be dashes sometimes.

The complex problem with this sheet is when he goes to order his steel (i.e. this sheet), he may only need phase A or just B...and when he enters that, it pulls in just that. But a good example right now is this...job 161343 also has a job called 161343_PORTAL. If he enters just 161343, that's all he needs for that week. But it still pulls _PORTAL. Same thing happens if he enters a job with phases but needs the one with no A, B, C, etc. It will still pull A, B, C. etc. and he doesn't need it .

So it is in this block:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value

That proceeds to this line vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

And the FindJobDir subroutine is 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

UPDATE: Interesting...I did you recommended sJob=rCell.value + "*" ...putting in a job that has phases but with no phase needed for that week, brings up type mismatch error...if I put in a phased job, it completes successfully.
You could do a try...catch block or an onerror statement to catch that error... so if it bombs with the type mismatch, then you can force it to do try it for a non-phased job?

catch block or an onerror statement's are new to me so researching them...

garak0410

Thinking out loud here...wonder if the xlWhole option would work but where?

dafyre

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

Thinking out loud here...wonder if the xlWhole option would work but where?

I'm unsure about that one as I've never used it.

garak0410

Though some research and assistance, looks like this might get me started...

Within this function:

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

Looking at adding the following:
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

Now to learn where to put this and what to do if it doesn't match...

garak0410

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

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
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

dafyre

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

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

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
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

What about the LookAt = xlWhole? Maybe you should...?

dim LookAt
set LookAt=xlWhole
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt)

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:

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

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
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

What about the LookAt = xlWhole? Maybe you should...?
dim LookAt
set LookAt=xlWhole
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt)

Tried that, now it says COMPILE ERROR, OBJECT REQUIRED. It Yellow Highlights the Function line and blue Highlights XlWhole after the Set LookAt = .

I wonder if I need to make the variables Explicit and Public?

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:

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

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
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

What about the LookAt = xlWhole? Maybe you should...?
dim LookAt
set LookAt=xlWhole
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt)
Tried that, now it says COMPILE ERROR, OBJECT REQUIRED. It Yellow Highlights the Function line and blue Highlights XlWhole after the Set LookAt = .

I wonder if I need to make the variables Explicit and Public?

What if you take out the dim, and LookAt=xlWhole completel, and remove the LookAt part on the last line so that it becomes..

Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath)

?