vba 查找 Excel 工作簿的工作表名称和行号

Find Worksheet Name and Row Number for an Excel Workbook

提问by AME

I am working with a workbook that contains three worksheets of data. Each worksheet has a Contract Number column. Certain contracts must be excluded and noted in a separate worksheet.

我正在使用包含三个数据工作表的工作簿。每个工作表都有一个合同编号列。某些合同必须排除在外，并在单独的工作表中注明。

I would like to create Excel VBA macro that:

我想创建 Excel VBA 宏：

1. Prompts the user to enter specific contract numbers to be excluded
2. Stores contract numbers
3. Searches all three worksheets' contract column for the contract numbers
4. Notes the unwanted contract details in a "summary" worksheet, which has already been created
5. Deletes the unwanted contract row entirely

1. 提示用户输入要排除的特定合同编号
2. 商店合同号
3. 在所有三个工作表的合同列中搜索合同编号
4. 在已经创建的“摘要”工作表中记录不需要的合同详细信息
5. 完全删除不需要的合同行

The macro should loop through this process below for 'n' number of contracts entered by the user.

对于用户输入的“n”个合约，宏应该循环执行下面的这个过程。

Public contString As String
Public x As Variant
Public xCount As Variant

Sub find()
contString = InputBox(Prompt:="Enter contract numbers to exclude(Comma Delimited).      Cancel to include all contracts.", _
      Title:="Exclude Contracts", Default:="1715478")
  x = Split(contString, ",")
  xCount = UBound(x) 'Number of contracts entered by user
End Sub

Sub SearchWS1()
Sheets("WS1").Activate
Columns("I:I").Select 'Contract Number Column
Selection.find(What:=x(i), After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
BKWS = ActiveCell.Worksheet.Name
BKRow = ActiveCell.Row
If BKRow > 0 Then
   Cname = Range("G" & BKRow)
   Cnumber = Range("I" & BKRow)
   Cvalue = Range("K" & BKRow)
   'Summarize Excluded Contract Info on Summary WS
   Range("Summary!B25").Value = "Exclusions:"
   Range("Summary!B26").Value = Cnumber
   Range("Summary!C26").Value = Cname
   Range("Summary!D26").Value = Cvalue
   'Select and Delete Contract
   Rows(ActiveCell.Row).Select
   Rows(BKRow).EntireRow.Delete
Else
   Call SearchWS2 'SearchWS2 is essentially the same as SearchWS1 and Calls SearchWS3 if contract isn't found. 
End If
End Sub

If the contract number doesn't exist in the first WS, I get an error like 'Object variable or With block not set'. Once I can fix this error, I will need to run this process through a loop for each contract number entered by the user. Any help with debugging the error or setting up a loop for this would be greatly appreciated.

如果第一个 WS 中不存在合同号，我会收到类似“对象变量或块未设置”的错误消息。一旦我可以修复这个错误，我将需要为用户输入的每个合同号通过一个循环来运行这个过程。任何有关调试错误或为此设置循环的帮助将不胜感激。

Thanks!

谢谢！

回答by Jon49

1. Use the InputBoxfor inputting contract numbers (let's say, comma delimited). Split the result using Splitfunction.
2. Store contract numbers on a separate worksheet that you hide (wks.visible=xlVeryHidden, where wksis a worksheetobject).
3. Find values using a multidimensional array to store the values.
4. Print 2D array to found worksheet using rFound=saArray(where rFoundis a rangeobject and saArrayis the 2D array.

1. 使用InputBox输入合同编号（比如说，逗号分隔）。使用Split函数拆分结果。
2. 将合同编号存储在您隐藏的单独工作表上（wks.visible=xlVeryHidden，其中wks是一个worksheet对象）。
3. 使用多维数组查找值来存储值。
4. 将二维数组打印到找到的工作表使用rFound=saArray（其中rFound是一个range对象，saArray是二维数组。

Make heavy use of recording macros to learn syntax.

大量使用录制宏来学习语法。

See this exampleon fast ways to retrieve and print to cells.

有关检索和打印到 的快速方法，请参阅此示例cells。

Update:

更新：

Sorry, this is pretty sloppy but I just threw it together and, obviously, it hasn't been tested. Hope this helps. Sorry, I also shouldn't be having you use advanced techniques like this, but it's hard for me to go back.

对不起，这很草率，但我只是把它放在一起，很明显，它还没有经过测试。希望这可以帮助。对不起，我也不应该让你使用这种高级技术，但我很难回去。

dim j as integer, k as integer, m as long, iContractColumn as integer
Dim x() as string, saResults() as string
dim vData as variant
dim wks(0 to 2) as worksheet

iContractColumn=????

set wks(0) = Worksheets("First")
set wks(1) = Worksheets("Second")
set wks(2) = Worksheets("Third")

redim saresults(1 to 100, 1 to 2)
m=0
'Loop thru worksheets
for j=0 to 2
  'Get data from worksheet
  vdata=wks(j).range(wks(j) _
    .cells(1,iContractColumn),wks(j).cells(rows.count,iContractColumn).end(xlup))
  'Loop through data
  for k=1 to ubound(vdata)
    'Loop through user criteria
    For i = 0 To UBound(x)
      'Compare user criteria to data
      if x(i)=cstr(vdata(k,1)) then
        'Capture the row and worksheet name
        m=m+1
        'If array is too small increase size
        if m>ubound(saresults) then 
          redim preserve saresults(1 to ubound(saresults)*2, 1 to 2)
        end if
        'Get name and row.
        saresults(m,1)=wks(j).name
        saresults(m, 2)=k
        exit for
      end if
    next i
  next k
next j

'Resize array to correct size
redim preserve saresults(1 to m, 1 to 2)
'Print results to a result page (you could also create hyperlinks here
'that would make it so the person can click and go to the respective page.
'You would have to do a loop for each result on the range.
with worksheets("Result Page")
  .range(.cells(1,1),.cells(m,2))=saresults
end with

回答by Tony Dallimore

I have little to add Jon49's answer which does seem to cover the basics. But I wish I had discovered Forms earlier in my VBA programming career. They can be a little confusing at first but, once mastered, they add enormously to the usability of a macro for very little effort.

我几乎没有添加 Jon49 的答案，它似乎涵盖了基础知识。但我希望我在我的 VBA 编程生涯中更早地发现了 Forms。一开始它们可能会有些混乱，但是一旦掌握了它们，它们就会大大增加宏的可用性，而无需付出太多努力。

Forms can be used to get values from the user (instead of InputBox) or can be used to give progress information to the user. I will only talk about the second usage. Your macro might take some time; has the user time to get a cup of coffee or will it finish in 5 seconds? I HATE programs that sit there saying "please wait - this may take from a few minutes to a few hours".

表单可用于从用户（而不是 InputBox）获取值或可用于向用户提供进度信息。我只讲第二种用法。您的宏可能需要一些时间；用户有时间喝一杯咖啡还是会在 5 秒内完成？我讨厌坐在那里说“请稍等 - 这可能需要几分钟到几个小时”的程序。

The following code loads a form into memory, shows it to the user and removes it from memory at the end. If you do not unload the form, it remains on the screen after the macro has ended which may be useful if you want to leave a message for the user. This form is show "modeless" which means the macro displays it and carries on. If shown "modal", the macro stops until the user has entered whatever information the form requires.

下面的代码将一个表单加载到内存中，将它显示给用户，最后将它从内存中删除。如果您不卸载表单，它会在宏结束后保留​​在屏幕上，如果您想给用户留言，这可能很有用。这种形式是显示“无模式”，这意味着宏显示它并继续。如果显示为“modal”，宏将停止，直到用户输入表单所需的任何信息。

  Load frmProgress
  Progress.Show vbModeless
  ' Main code of macro
  Unload frmProgress

There are no end to the web sites offering tutorials on Forms so I will mainly describe the what rather than how.

提供表单教程的网站没有尽头，所以我将主要描述什么而不是如何。

Within the VB Editor, Insert a UserForm. Drags the bottom and right edges if you want it bigger. Use the Properties Window to change the Name to frmProgress.

在 VB 编辑器中，插入一个用户窗体。如果您希望它更大，请拖动底部和右侧边缘。使用属性窗口将名称更改为 frmProgress。

Drag four labels from the Tool Box and arrange them in a line. Set the caption of label 1 to "Worksheet " and the caption of label 3 to "of". Name label 2 "lblWSNumCrnt" and name label 4 "lblWSNumTotal".

从工具箱中拖动四个标签并将它们排列成一行。将标签 1 的标题设置为“工作表”，将标签 3 的标题设置为“of”。将标签 2 命名为“lblWSNumCrnt”，将标签 4 命名为“lblWSNumTotal”。

Add the following around "for j = 0 to 2"

在“for j = 0 to 2”周围添加以下内容

frmProgress.lblWSNumTotal.Caption = 3
for j = 0 to 2
  frmProgress.lblWSNumCrnt.Caption = j + 1
  DoEvents

This means the user will see the following with n stepping from 1 to 3 as the macro progesses:

这意味着用户将在宏进行时看到以下 n 从 1 到 3 的步进：

Worksheet    n  of     3

Add another four labels for row number, and the following code around the k loop:

为行号添加另外四个标签，并在 k 循环周围添加以下代码：

frmProgress.lblRowNumTotal.Caption = ubound(vdata, 1)
for k = 1 to ubound(vdata, 1)
  frmProgress.lblRowNumCrnt.Caption = k
  DoEvents

Now the user will see something like:

现在用户将看到如下内容：

Worksheet    2  of     3
      Row  1456 or  2450

The above technique is simple and does not involve any change to Jon49's code. The following technique, borrowed from Wrox's excellent Excel VBA Programmer's Reference, is a little more complicated but gives your macro a more professional appearance.

上述技术很简单，不涉及对Jon49 的代码进行任何更改。以下技术是从 Wrox 的优秀Excel VBA 程序员参考借来的，稍微复杂一些，但让您的宏看起来更专业。

Create a label that runs across the entire form. Name it "lblToDo" and colour it white. Create another label of the same size over the top. Name it "lblDone" and colour it black.

创建一个贯穿整个表单的标签。将其命名为“lblToDo”并将其着色为白色。在顶部创建另一个相同大小的标签。将其命名为“lblDone”并将其着色为黑色。

Create a copy of the code to count the rows in each sheet at the top so you can calculate the total number of rows, "TotalRowsTotal", before you do anything else.

创建代码副本以计算顶部每个工作表中的行数，以便您可以在执行其他任何操作之前计算总行数“TotalRowsTotal”。

Create a new variable "TotalRowsCrnt", initialise it to zero and add one to it for every row in every worksheet.

创建一个新变量“TotalRowsCrnt”，将其初始化为零并为每个工作表中的每一行添加一个。

Within the inner loop, add:

在内部循环中，添加：

frmProgress.lblToDo.Width =  _
        frmProgress.lblDone.Width * TotalRowsCrnt / TotalRowsTotal   

For Excel 2003, which all the organisations I work with still use, this gives a progress bar with the black Done label steadily covering the white ToDo label. Later version of Excel may offer a progress bar control as standard.

对于 Excel 2003，我与之合作的所有组织仍在使用，这提供了一个进度条，黑色的“完成”标签稳定地覆盖了白色的“待办事项”标签。更高版本的 Excel 可能会提供一个标准的进度条控件。

I hope this gives you some ideas for making your macros more attractive to your users.

我希望这会给您一些想法，让您的宏对您的用户更具吸引力。