VBA Hint Needed - Change Way An Existing Project Grabs Data
-
@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath)
If I do that, it does finish the Macro but doesn't do the exact match...guessing now if Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath) is what I needed, then I perhaps need a conditional statement now?
-
What is interesting about learning while you debug is how lurching it can be...like right now, I don't think we need to key on strPath but insread sJob...sJob is the parsed strPath with just the job number...
It is declared and used in another area of code and not visible to this function so I need to get around that.
-
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
What is interesting about learning while you debug is how lurching it can be...like right now, I don't think we need to key on strPath but insread sJob...sJob is the parsed strPath with just the job number...
It is declared and used in another area of code and not visible to this function so I need to get around that.
Pass in sJob as another parameter to the function?
-
When this macro goes to call FindJobDir, it is included in this line with sJob:
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?
-
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
When this macro goes to call FindJobDir, it is included in this line with sJob:
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?
Possible. Does vJobFolders return a single folder, an array of folders?
-
@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:
When this macro goes to call FindJobDir, it is included in this line with sJob:
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?
Possible. Does vJobFolders return a single folder, an array of folders?
When debugging the code step by stepAt one point, vJobFolders does get the value of W2 assigned to it...that is after it is verified that is an existing folder at the network location......SJob equals what is typed into Column W but it still doesn't guarantee it will be at the network location that we are looking for an exact match for...
So thinking aloud, maybe if sJobs doesn't equal the value of sJob at some point, then it should throw up a message that the job folder doesn't exist. That way, not only will it match exactly, but also alert the user to a possible typo...
Maybe that is a little simplistic for my neophyte VBA mind...
-
@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:
When this macro goes to call FindJobDir, it is included in this line with sJob:
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?
Possible. Does vJobFolders return a single folder, an array of folders?
When debugging the code step by stepAt one point, vJobFolders does get the value of W2 assigned to it...that is after it is verified that is an existing folder at the network location......SJob equals what is typed into Column W but it still doesn't guarantee it will be at the network location that we are looking for an exact match for...
So thinking aloud, maybe if sJobs doesn't equal the value of sJob at some point, then it should throw up a message that the job folder doesn't exist. That way, not only will it match exactly, but also alert the user to a possible typo...
Maybe that is a little simplistic for my neophyte VBA mind...
And interesting...I think I may be getting somewhere but now the dreaded NEXT WITHOUT FOR message. I did next this idea in a FOR statement which reads:
For i = 0 To UBound(vJobFolders)
Should we do one for sResults?
Again, thinking out loud here...
-
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
Maybe that is a little simplistic for my neophyte VBA mind...
The simpler you can make it, the better off everybody will be, lol.
-
@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:
Maybe that is a little simplistic for my neophyte VBA mind...
The simpler you can make it, the better off everybody will be, lol.
I still have a lot to learn, no time to learn it and still be Mr. Jack of All Trades SOLO IT Guy...I've literally poked at this code all week in between day to day and not a lot to show but perhaps closer to the solution.
-
@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:
Maybe that is a little simplistic for my neophyte VBA mind...
The simpler you can make it, the better off everybody will be, lol.
I still have a lot to learn, no time to learn it and still be Mr. Jack of All Trades SOLO IT Guy...I've literally poked at this code all week in between day to day and not a lot to show but perhaps closer to the solution.
Hopefully you'll get it! Keep poking at it... just don't miss and accidentally poke the bear with a stick!
