查找和替换问题: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屋!