Excel VBA 在找到所有不同结果之前，有几个搜索标准和循环。

我是非常新的 VBA 而且我有很短的时间，所以我道歉，如果没有遵循论坛的所有建议。 我非常感谢您可以渲染的任何帮助！

目的

:

Sheet1按关键字搜索 /活动：，网站地址：，描述：，所有者：，评分：子类型：和 DATE_B:/

一旦找到关键字，偏移量 /0,1/

复制值

列出 2 字母列如此： Permit_Type, Permit_Date, Permit_Address, Permit_Desc, Owner 和 Permit_Val/

从纸张插入复制的值 1 在相应的列中

重复脚本，直到不再找到所有关键字表1。 换句话说，继续在整个纸上。 1.

什么工作

:

创建工作表上列的名称 2

脚本复制并插入找到的第一个值。

什么不起作用

:

脚本在找到第一个值后停止。

着名的问题：

最初我复制了/将值插入到范围内相同的表格1 O2:U2. 我很难删除此命令，因为我只需要在表格中插入这些值

数据看起来像这样：关于 100 记录

, 大多数关键词都在a列中，然后 rest 在电子遗憾列中，我无法提供更好的主意！

'Column A Column B Column C Column D Column E Column F Column G G 

 'Activity: B13-0217 Type: BUILD-M Sub Type: Porch Status: ISSUED

 '



 'Parcel: DATE_B: 09/13/2013 Sq Feet: 

 'Site Address: 123 Main St 

 'Description: Patio cover 150 sqft 

 'Applicant: ABC Contracting Phone: 123-456-7890 

 'Owner: Jane Smith Phone: 123-456-7890 

 'Contractor: ABC Contracting Phone: 123-456-7890 

 'Occupancy: Use: Class: Insp Area: 

 'Valuation: $3,200.00 Fees Req: $256.90 Fees Col: $256.90 Bal Due: $0.00 



 'Activity: B13-0224 Type: BUILD-M Sub Type: Deck Status: ISSUED

 'Parcel: DATE_B: 09/27/2013 Sq Feet: 

 'Site Address: 234 South St 

 'Description: Install a 682 sqft deck on the east side of the building 

 'Applicant: BCA Contracting Phone: 234-567-1234 

 'Owner: Joe Smith Phone: 234-567-1234 

 'Contractor: BCA Contracting Phone: 234-567-1234 

 'Occupancy: Use: Class: Insp Area: 

 'Valuation: $28,000.00 Fees Req: $1,408.60 Fees Col: $1,408.60 Bal Due: $0.00

以下是我收集的脚本。 任何帮助都会非常感激！

Sub Lafayette_Permit_arrangement_macro//



' This Macro is intended to arrange the monthly Lafayette Permit

' data so that specific data is extracted and organized in a more

' usable format for mass import.





'Permit Number

Cells.Find/What:="Activity:", After:=ActiveCell, LookIn:=xlFormulas, _

 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

 MatchCase:=False, SearchFormat:=False/.Offset/0, 1/.Select

 Selection.Copy

Range/"O2"/.Select

 ActiveSheet.Paste

'Permit Type

 Cells.Find/What:="Sub Type:", After:=ActiveCell, LookIn:=xlFormulas, _

 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

 MatchCase:=False, SearchFormat:=False/.Offset/0, 1/.Select

 Selection.Copy

 Range/"P2"/.Select

 ActiveSheet.Paste

'Permit Issue Date

 Cells.Find/What:="DATE_B:", After:=ActiveCell, LookIn:=xlFormulas, _

 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

 MatchCase:=False, SearchFormat:=False/.Offset/0, 1/.Select

 Selection.Copy

 Range/"Q2"/.Select

 ActiveSheet.Paste

'Permit Address

 Cells.Find/What:="Site Address:", After:=ActiveCell, LookIn:=xlFormulas, _

 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

 MatchCase:=False, SearchFormat:=False/.Offset/0, 1/.Select

 Selection.Copy

 Range/"R2"/.Select

 ActiveSheet.Paste

'Permit Description

 Cells.Find/What:="Description:", After:=ActiveCell, LookIn:=xlFormulas, _

 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

 MatchCase:=False, SearchFormat:=False/.Offset/0, 1/.Select

 Selection.Copy

 Range/"S2"/.Select

 ActiveSheet.Paste

'Permit Owner

 Cells.Find/What:="Owner:", After:=ActiveCell, LookIn:=xlFormulas, _

 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

 MatchCase:=False, SearchFormat:=False/.Offset/0, 1/.Select

 Selection.Copy

 Range/"T2"/.Select

 ActiveSheet.Paste

'Permit Value

 Cells.Find/What:="Valuation:", After:=ActiveCell, LookIn:=xlFormulas, _

 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

 MatchCase:=False, SearchFormat:=False/.Offset/0, 1/.Select

 Selection.Copy

 Range/"U2"/.Select

 ActiveSheet.Paste



 Range/"O2:U2"/.Select

 Application.CutCopyMode = False

 Selection.Copy

 Sheets/"Sheet2"/.Select

 Range/"A2"/.Select

 ActiveSheet.Paste

 Sheets/"Sheet2"/.Select

 Range/"A1"/.Select



 Application.CutCopyMode = False

 'Add PermitNo column to Sheet2

 ActiveCell.FormulaR1C1 = "Permit_No"

 Range/"A1"/.Select

 'Add PermitType column to Sheet2

 ActiveCell.FormulaR1C1 = "Permit_Type"

 Range/"B1"/.Select

 'Add PermitDate column to Sheet2

 ActiveCell.FormulaR1C1 = "Permit_Date"

 Range/"C1"/.Select

 'Add PermitAdd column to Sheet2

 ActiveCell.FormulaR1C1 = "Permit_Address"

 Range/"D1"/.Select

 'Add PermitDesc column to Sheet2

 ActiveCell.FormulaR1C1 = "Permit_Desc"

 Range/"E1"/.Select

 'Add PermitOwner column to Sheet2

 ActiveCell.FormulaR1C1 = "Owner"

 Range/"F1"/.Select

'Add PermitVal column to Sheet2

 ActiveCell.FormulaR1C1 = "Permit_Val"

 Range/"G1"/.Select









End Sub