none
Find and Replace Issue : MS Excel cannot find a match RRS feed

  • Question

  • Hi Folks -

    I'm running into an issue when performing a Find& Replace on multiple worksheets in the same workbook.  What I do is populate (3) sheets in Workbook1 with content from various other workbooks.  Then, since columns C & D across all workbooks are blank, I perform a find and replace using "" (blank) to populate Year and Period.  However, when I do this across all sheets, it gives me an error saying:

    "Microsoft Excel cannot find a match."

    If I only leave 1 find & replace section in there following sheet 1, for instance, I get no errors. It's only when I try to do it across more than 1 sheet.  Any ideas?

    Also, I was hoping you could help me out with putting the "Clear" sections and "Find & Replace" sections within a forloop since each section int he clear and find&replace are the same across all workbooks. I wantt o keep it dynamic and not hard code sheets when I can help it.

    Attached is my code:

    Set objExcel = CreateObject("Excel.Application")
    'objExcel.Visible = True
    
    '::-- Declare argurments passed from batch script --::'
    Dim args, PERIOD, YEAR, HC_EXCEL_PREPPATH, HC_TEMP_UTIL_WB, HC_CON, HC_SEC, HC_EMP
    set args = Wscript.arguments
    
    PERIOD= args(0)
    YEAR= args(1)
    HC_EXCEL_PREPPATH= args(2)
    HC_TEMP_UTIL_WB= args(3)
    HC_CON= args(4)
    HC_SEC= args(5)
    HC_EMP= args(6)
    
    WScript.Echo "Performing Headcount Submission Prep..."
    
    '::-- Set pathing and names of Excel Workbooks used for Headcount Submission Process --::'
    
    Set objWorkbook1= objExcel.Workbooks.Open( HC_EXCEL_PREPPATH & HC_TEMP_UTIL_WB )
    Set objWorkbook2= objExcel.Workbooks.Open( HC_EXCEL_PREPPATH & HC_CON )
    Set objWorkbook3= objExcel.Workbooks.Open( HC_EXCEL_PREPPATH & HC_SEC )
    Set objWorkbook4= objExcel.Workbooks.Open( HC_EXCEL_PREPPATH & HC_EMP )
    
    '::-- Clear all sheets' content of TEMP_Utility to prepare for data stagging  --::'
    
    objRange = objWorkbook1.Worksheets("Sheet1").Range("A2:Z10000").Clear
    objRange = objWorkbook1.Worksheets("Sheet2").Range("A2:Z10000").Clear
    objRange = objWorkbook1.Worksheets("Sheet3").Range("A2:Z10000").Clear
    
    '::-- Contractors Headcount File --::'
    objRange = objWorkbook2.Worksheets("Sheet1").Range("E4:E10000").Copy
    objWorkbook1.Worksheets("Sheet1").Range("A2:A10000").PasteSpecial objRange
    
    objRange = objWorkbook2.Worksheets("Sheet1").Range("AA4:AA10000").Copy
    objWorkbook1.Worksheets("Sheet1").Range("B2:B10000").PasteSpecial objRange
    
    objRange = objWorkbook2.Worksheets("Sheet1").Range("V4:V10000").Copy
    objWorkbook1.Worksheets("Sheet1").Range("E2:E10000").PasteSpecial objRange
    
    '::-- Perform replacements --::'
    Set objWorksheet1 = objWorkbook1.Worksheets("Sheet1")
    xlUp = -4162
    Set LastCell = objWorksheet1.Range("A" & objWorksheet1.Rows.Count).End(xlUp)
    objWorkbook1.Worksheets("Sheet1").Range("C2:C" & LastCell.Row ).Replace "", YEAR
    objWorkbook1.Worksheets("Sheet1").Range("D2:D" & LastCell.Row ).Replace "", PERIOD
    
    '::-- Secondees Headcount File --::'
    objRange = objWorkbook3.Worksheets("Sheet1").Range("E5:E50000").Copy
    objWorkbook1.Worksheets("Sheet2").Range("A2:A10000").PasteSpecial objRange
    
    objRange = objWorkbook3.Worksheets("Sheet1").Range("AA5:AA10000").Copy
    objWorkbook1.Worksheets("Sheet2").Range("B2:B10000").PasteSpecial objRange
    
    objRange = objWorkbook3.Worksheets("Sheet1").Range("V5:V10000").Copy
    objWorkbook1.Worksheets("Sheet2").Range("E2:E10000").PasteSpecial objRange
    
    '::-- Perform replacements --::'
    Set objWorksheet2 = objWorkbook1.Worksheets("Sheet2")
    xlUp = -4162
    Set LastCell = objWorksheet2.Range("A" & objWorksheet2.Rows.Count).End(xlUp)
    objWorkbook1.Worksheets("Sheet2").Range("C2:C" & LastCell.Row ).Replace "", YEAR
    objWorkbook1.Worksheets("Sheet2").Range("D2:D" & LastCell.Row ).Replace "", PERIOD
    
    '::-- Employees Headcount File --::'
    objRange = objWorkbook4.Worksheets("Sheet1").Range("E4:E10000").Copy
    objWorkbook1.Worksheets("Sheet3").Range("A2:A10000").PasteSpecial objRange
    
    objRange = objWorkbook4.Worksheets("Sheet1").Range("AA4:AA10000").Copy
    objWorkbook1.Worksheets("Sheet3").Range("B2:B10000").PasteSpecial objRange
    
    objRange = objWorkbook4.Worksheets("Sheet1").Range("V4:V10000").Copy
    objWorkbook1.Worksheets("Sheet3").Range("E2:E10000").PasteSpecial objRange
    
    '::-- Perform replacements --::'
    Set objWorksheet3 = objWorkbook1.Worksheets("Sheet3")
    xlUp = -4162
    Set LastCell = objWorksheet3.Range("A" & objWorksheet3.Rows.Count).End(xlUp)
    objWorkbook1.Worksheets("Sheet3").Range("C2:C" & LastCell.Row ).Replace "", YEAR
    objWorkbook1.Worksheets("Sheet3").Range("D2:D" & LastCell.Row ).Replace "", PERIOD
    
    '::-- Exit protocol--::'
    
    objWorkbook1.Save
    objWorkbook1.Close
    
    objWorkbook2.Save
    objWorkbook2.Close
    
    objWorkbook3.Save
    objWorkbook3.Close
    
    objWorkbook4.Save
    objWorkbook4.Close
    
    objExcel.Application.Quit
    WScript.Quit

    Thanks!

    Sunday, June 25, 2017 1:17 PM

All replies

  • Since you clear the sheets entirely, why not just set the value directly?

    For example, change this:

    objWorkbook1.Worksheets("Sheet1").Range("C2:C" & LastCell.Row ).Replace "", YEAR

    to this:

    objWorkbook1.Worksheets("Sheet1").Range("C2:C" & LastCell.Row ).Value = YEAR

    Tuesday, June 27, 2017 8:00 PM
  • That worked great!! Thank you!
    Wednesday, June 28, 2017 10:41 PM