Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: Help with a search and replace routine.

  1. #1

    Help with a search and replace routine.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all.
    Would someone be willing to help me refine my search and replace routine? The programming is getting beyond my capabilities. It has been working fine until the schedulers added a new column to the report. Please allow me to explain:

    I have two excel spreadsheets which are essentially identical except for the comment field. Each week a new spreadsheet comes out (Sheet A). Employees are to update the comments on their jobs from the previous old spreadsheet (Sheet B). I have written a VB routine to do this by simply extracting the job number in sheet A and searching for the same job number in sheet B. I then go to the comment field in sheet B and paste it in the same respective spot in the new sheet A. Below is the code to do this

    The problem I am having now is our scheduling department has added a new field to the job. So now I have to make sure two fields match. Can you please help to modify the underlined statement to search for two fields to match simultaneously. They must match so I can be sure the comment is the correct one.

    Code:
    Public Sub Process_billing_data()
     
     Dim CG As String
     Dim GCell As Range
     Dim x, y, z As Integer
     Dim Mypath$, Mycomment$
     x = 1
     TestER = False
      
      Sheets("Report out").Activate
    
    
       
       For i = 2 To 100
           
            CG = Cells(x, 5) ' Get the Job number
            Sheets("JL").Activate
            Set GCell = ActiveSheet.Cells.Find(CG)
                If Not GCell Is Nothing Then
                    With GCell
                    On Error GoTo ErrorHandler
                            
                    Mycomment$ = .Offset(0, 6).Value
                  
                    End With
                    
                    Sheets("Report out").Activate
                    With ThisWorkbook.ActiveSheet.Cells(x, 1)
                    .Offset(0, 6).Value = Mycomment$
                    End With
                Else
                MsgBox ("Loop Stopped")
                Sheets("Report out").Activate
                End If
            x = x + 1
            Next i
    
    
    ActiveWorkbook.Close SaveChanges:=True
    
    
    ErrorHandler:
    TestER = True
    Resume Next
    End Sub
    Last edited by BACK2BASIC; 2014-02-07 at 09:40 PM.

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    could you upload a sample workbook that this code refers to?
    I do a similar thing with some sheets I work with daily.
    There is a lot of simplification we could do with your code.

  3. #3
    Quote Originally Posted by Simi View Post
    could you upload a sample workbook that this code refers to?
    I do a similar thing with some sheets I work with daily.
    There is a lot of simplification we could do with your code.
    I have uploaded the Excel SS file: Schedule 2_10_14. Sheet "Report Out" is the new sheet with no comments. Sheet "Schd complete" is the previous weeks report with the comments that need to be extracted and placed into Sheet "Report out". Field H is the new Field added to the report which is making it difficult for me. Because now both Field A and Field H must match. There can be several identical Field A and several different field B. Thank you for help in any way you can.
    Attached Files Attached Files

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I have made a new sub for you to run, check it out Sub simi_report_out()

    Also, just for clarification. This takes the comments from the "Schd complete" tab and moves them to the "Report out" tab if both column A and H match.
    Attached Files Attached Files

  5. #5
    Quote Originally Posted by Simi View Post
    I have made a new sub for you to run, check it out Sub simi_report_out()

    Also, just for clarification. This takes the comments from the "Schd complete" tab and moves them to the "Report out" tab if both column A and H match.
    OMG! This is beautiful code. You are so kind to help me and write this routine. It works great! I must be honest, it is very efficient and I need to run it a few times to understand fully how it works.

    THANK YOU!

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I'm glad you liked it.
    I didn't know if the data you had, needed to be in any particular order and sorting before running my comparisons makes it lots faster.
    So I added a counter in column R to use to sort it back to the original sorting after I ran the comparisons and copied data.
    I should have probably commented out the lCounter = lCounter +1 line so it could skip that step, I was using that for testing efficiency.
    I had 2 nested for/next instead of the for and do you see. The 2 nested for loops had over 11,000 iterations, where the current version had less than 200 making it much more efficient.
    If you have any questions about the code I would love to answer them to help you learn.

  7. #7
    Quote Originally Posted by BACK2BASIC View Post
    OMG! This is beautiful code. You are so kind to help me and write this routine. It works great! I must be honest, it is very efficient and I need to run it a few times to understand fully how it works.

    THANK YOU!
    Hi, there is one issue, sorry I was not clear on and I am trying to figure out the best way to fix. Even though for the most part the two sheets are equal, the new job sheet (Report out - Sheet 1) that is generated each week could and will have more or less jobs on it then the previous week (Schd complete - Sheet 2).

    This is because jobs get added each week and deleted as they are completed. Therefore, the sort order can be different and throws off the entire count? The only way I can think of to fix this is to take each row from sheet A (Report out) and search each row in sheet B (Schd completed). In others, the routine must run through the loop the total number of rows in (Sheet B) Schd complete searching every row in Sheet A (Report out). Do you agree?

  8. #8
    We were writing to each other at the same time. I just got your message. Thank you and yes I will have some questions. I need to understand and digest this code first. If you are willing to help me learn more that is awesome because I do want to become better. Your code is much more understandable and simpler. I am a crude step by step programmer.

  9. #9
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    The way this macro works.

    1. I add column R on both sheets and simply put the current row number in cell R2 down to R(the last row with data). This is so I can resort the sheets back to the original sorting.
    2. I then cycle through all of the items from sheet1, "Report Out" this is the For/Next block.
    3. The Do While loop cycles through all of the items on sheet2, "Schd complete" until it finds a match. (I think this may be where your problem is, if there isn't a match we need to reset the row counter lRowCurrent2 back to 2 to start from the top of sheet2 again for the next item from sheet1)
    4. If there is a match in both column A and H it copies the data from Q, then moves to the next row in sheet1.

    Replace this portion of the code with this.
    Code:
    lRowCurrent2 = 2
    For lRowCurrent1 = 2 To lRowMax1
        Do While lRowCurrent2 <= lRowMax2
            lCounter = lCounter + 1
            If Worksheets(1).Range("A" & lRowCurrent1) = Worksheets(2).Range("A" & lRowCurrent2) And Worksheets(1).Range("H" & lRowCurrent1) = Worksheets(2).Range("H" & lRowCurrent2) Then
                Worksheets(1).Range("Q" & lRowCurrent1) = Worksheets(2).Range("Q" & lRowCurrent2)
                lRowCurrent2 = lRowCurrent2 + 1
                Exit Do
            End If
            lRowCurrent2 = lRowCurrent2 + 1
            If lRowCurrent2 > lRowMax2 Then
                lRowCurrent2 = 2 'reset to top of sheet if no match was found
            End If
        Loop
    Next lRowCurrent1
    please note this is the portion that was added.
    Code:
     If lRowCurrent2 > lRowMax2 Then
                lRowCurrent2 = 2 'reset to top of sheet if no match was found
            End If
    Because we sort both of the sheets, we don't want to always start from the top of sheet2 for all of the comparisons. That will run slow if you have a lot of records.
    If you have a match that is on row 50 of sheet 2, the next item from sheet 1 can't be above that because they are sorted. We only want to start from the top again if we don't find a match.
    We could also add another variable for "last row that was copied" and reset the lRowCurrent2 to that variable so we could resume the search from row 50, instead of the top.

  10. #10
    Great Explanation. I understand what your doing but with the code added we are in endless loop. lRowCurrent2 never becomes greater then lRowMax2 because it is reset by the if statement. To test this simply copy the first row of Report out sheet and make it different. The compare test fails and we never move on from there.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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