ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    VBA Hint Needed - Change Way An Existing Project Grabs Data

    IT Discussion
    5
    72
    7.6k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • dafyreD
      dafyre @garak0410
      last edited by 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)

      ?

      garak0410G 1 Reply Last reply Reply Quote 0
      • garak0410G
        garak0410 @dafyre
        last edited by

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

        1 Reply Last reply Reply Quote 0
        • garak0410G
          garak0410
          last edited by

          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.

          dafyreD 1 Reply Last reply Reply Quote 0
          • dafyreD
            dafyre @garak0410
            last edited by

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

            1 Reply Last reply Reply Quote 0
            • garak0410G
              garak0410
              last edited by

              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 ?

              dafyreD 1 Reply Last reply Reply Quote 0
              • dafyreD
                dafyre @garak0410
                last edited by

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

                garak0410G 1 Reply Last reply Reply Quote 0
                • garak0410G
                  garak0410 @dafyre
                  last edited by

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

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

                  garak0410G dafyreD 2 Replies Last reply Reply Quote 0
                  • garak0410G
                    garak0410 @garak0410
                    last edited by

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

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

                    1 Reply Last reply Reply Quote 0
                    • dafyreD
                      dafyre @garak0410
                      last edited by

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

                      garak0410G 1 Reply Last reply Reply Quote 0
                      • garak0410G
                        garak0410 @dafyre
                        last edited by

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

                        dafyreD 1 Reply Last reply Reply Quote 0
                        • dafyreD
                          dafyre @garak0410
                          last edited by

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

                          1 Reply Last reply Reply Quote 0
                          • 1
                          • 2
                          • 3
                          • 4
                          • 3 / 4
                          • First post
                            Last post