Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2009
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel loop - offset problem

    I give up. I'm sure this is something very simple, but I just can't get it. I have to loop through an expense report from AP and resolve a series of different name spellings so I can group, sort and pass on to managers.

    The loop I have seems to work fine in dealing with name differentials, but it replaces the value in a delivered column (J). They want all original columns retained so I need for evaluate value in J and just put my relacement text in column K.

    How is offset for this written so that it works properly? I've enclosed a parsed down version of my file.

    Appreciate a more knowledgeable insight than I seem to have.
    Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand do these changes do what you want:
    Set Rng = wsh.Columns("J").Find(What:=StrFind, LookIn:=xlValues, LookAt:=xlPart)
    Rng.Offset(0, 1).Value = StrReplace
    Set Rng = wsh.Columns("J").FindNext(Rng)

    I changed the cells to columns to only look in Col J (to ensure that only values in Col J are find). Rng.offset(0,1) means 0 rows down and 1 column to the right from rng (moving from Col J to Col K)

    [Are you planning on having a separate find replace for each name? It may make more sense to have a worksheet with a list of name and replacement and then loop through the list in conjunction with the code. The worksheet would be easier to maintain than separate routines or even a routines with Array lists. With just 4 names as this appears, an array list would not be too bad however...]
    Steve

    PS These lines could do the arrays to have code for all in 1 procedure :

    Dim Rng As Range
    Dim vFind, vReplace
    Dim i As Integer
    'names to find add more as needed...
    vFind = Array("Valez", "Wagner", "Cumber", "Bowles")
    'names to replace with. add more as needed...
    vReplace = Array("Valez", "Wagner", "McCumber", "Bowles")
    For i = LBound(vFind) To UBound(vFind)
    StrFind = vFind(i)
    StrReplace = vReplace(i)
    For Each wsh In ActiveWindow.SelectedSheets 'Replace in selected sheets only
    ...
    Next wsh
    Next i
    End Sub
    Last edited by sdckapr; 2011-03-13 at 20:01. Reason: Added PS

  3. #3
    New Lounger
    Join Date
    May 2009
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    You anticipated my need perfectly; I do plan to replace each name and I would have written separate subs to do so. The array approach is genius to me. One item. I took your suggestions and ran the sub with the array logic. It updates only the first occurrence of each name and it also still modifies value in column J. Either I have a typo or another line of code may be required. Can you take a look?
    Sub test()
    Dim StrFind As String
    Dim StrReplace As String
    Dim strAddress As String
    Dim wsh As Worksheet
    Dim Rng As Range
    Dim vFind, vReplace
    Dim i As Integer
    'names to find add more as needed...
    vFind = Array("Valez", "Wagner", "Cumber", "Bowles")
    'names to replace with. add more as needed...
    vReplace = Array("Valez", "Wagner", "McCumber", "Bowles")
    For i = LBound(vFind) To UBound(vFind)
    StrFind = vFind(i)
    StrReplace = vReplace(i)
    For Each wsh In ActiveWindow.SelectedSheets 'Replace in selected sheets only
    Set Rng = wsh.Columns("J").Find(What:=StrFind, LookIn:=xlValues, LookAt:=xlPart)
    Rng.Offset(0, 1).Value = StrReplace

    If Not Rng Is Nothing Then
    strAddress = Rng.Address
    Do
    Rng.Value = StrReplace
    Set Rng = wsh.Columns("j").FindNext(Rng)

    If Rng Is Nothing Then
    Exit Do
    End If
    Loop While Rng.Address <> strAddress
    End If
    Next wsh
    Next i
    End Sub

    Also, they may provide variables of same name: Mc Cumber, MCCUMBER, MCCmbr; assume I'll have to account for each of these in the array, correct?
    Last edited by muley2; 2011-03-13 at 21:09. Reason: 1 more thought

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try this (note the differences in several lines):
    Code:
    Sub Test2()
    Dim StrFind As String
    Dim StrReplace As String
    Dim strAddress As String
    Dim wsh As Worksheet
    Dim Rng As Range
    Dim vFind, vReplace
    Dim i As Integer
    'names to find add more as needed...
    vFind = Array("Valez", "Wagner", "Cumber", "Bowles")
    'names to replace with. add more as needed...
    vReplace = Array("Valez", "Wagner", "McCumber", "Bowles")
    For i = LBound(vFind) To UBound(vFind)
    StrFind = vFind(i)
    StrReplace = vReplace(i)
    For Each wsh In ActiveWindow.SelectedSheets 'Replace in selected sheets only
    Set Rng = wsh.Columns("J").Find(What:=StrFind, LookIn:=xlValues, LookAt:=xlPart)
    If Not Rng Is Nothing Then
    strAddress = Rng.Address
    Do
    Rng.Offset(0, 1).Value = StrReplace
    Set Rng = wsh.Columns("J").FindNext(Rng)
    If Rng Is Nothing Then
    Exit Do
    End If
    Loop While Rng.Address <> strAddress
    End If
    Next wsh
    Next i
    End Sub

    For the variations like Mc Cumber and Mccumber, I solved in the code by finding "Cumber". MCCmbr would be a new item in each of the arrays. The order in the array does not matter...

    If the list is going to be very long, it may be worthwhile to eliminate the arrays and just create a worksheet of the find and "new col additions" (since you are not replacing). This may be easier to maintain...

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    muley2 (2011-03-14)

  6. #5
    New Lounger
    Join Date
    May 2009
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Steve, can't thank you enough for this. It works just like I wanted. Your code makes total sense to me; I understood the concept of offset, but it always intrigues me on where the placement of the line of code can impact a successful process.

    Before, the manager just did one sort and sent this out and let the recipients have at it to find and delete rows to get their items to review. This will save a huge amount of time for about 40+ people. A really great and cost saving solution. Thanks again.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am glad I could help.

    You may want to step through the 2 pieces of code on a small sample to see the differences that one line placement does. In yours with the line OUTSIDE the Do... Loop it is only replace once (for the initial find). By having it in the Do...Loop (where it was in mine and in your original code) it happens with the find and the Find nexts...

    But most of the work was yours: Notice that the brunt of the code is what you started with. I changed the find from all cells to just col J, and put results in the next column, but changed nothing else (you moved the rng.offset line outside the do...loop). I I then suggested and created the larger loop through the array list to change multiple names with one routine.

    Your replace in addition to the last name, could include the first name in the form desired so the k Column is "complete":
    vReplace = Array("Valez, James P", "Wagner, Angela R", "McCumber, Micheal", "Bowles, David M")

    [is it really Micheal?, the typical spelling is Michael, but I none use this only Mike or Micheal]

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •