查找和替换问题：MS Excel找不到匹配项 [英] Find and Replace Issue : MS Excel cannot find a match

问题描述

Hi Folks -

Hi Folks -

我在执行Find& amp;时遇到问题替换同一工作簿中的多个工作表。 我所做的是在Workbook1中填充（3）表格，其中包含来自其他各种工作簿的内容。 然后，由于列C&在所有工作簿中，
为空白，我执行查找并使用"&"; （空白）填写年份和期间。 但是，当我在所有工作表中执行此操作时，它会向我显示错误消息：

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无法找到匹配项。"

"Microsoft Excel cannot find a match."

如果我只留下1个发现&例如，替换下面第1页中的部分，我没有错误。只有当我尝试在超过1张纸上完成时才会这样做。 任何想法？

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?

另外，我希望你可以帮我把"清除"部分和"查找&替换” forloop中的部分，因为他清楚并找到和替换的每个部分在所有工作簿中都是相同的。我想保持动态
，而不是硬代码表，当我可以提供帮助时。

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.

附上我的代码：

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

谢谢！

推荐答案

由于您完全清除了工作表，为什么不直接设置该值？

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

例如，更改此：

objWorkbook1.Worksheets（" Sheet1"）。Range（" C2：C"&LastCell.Row）.Replace""，YEAR

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

对此：

objWorkbook1.Worksheets（& QUOT;工作表Sheet"）范围（" C2：C" &安培; LastCell.Row）.Value = YEAR

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

这篇关于查找和替换问题：MS Excel找不到匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！