哪一个更好，怎么做？公式还是宏？ [英] Which one would be better and how to do it? Formula or Macro?

问题描述

我有一个电子表格，我想跟踪他们及其到期日期。其中一张是我单位的每个人。我想要另一张表来提取护照过期或者还没有护照的人的姓名。我使用这个表达式来取名字 = IF（OR（''Whole Squad''！B2 =None，''Whole Squad''！B2 =exp），''Whole Squad ''！A2，）但如果我使用这个，它会将名字放在相应的单元格中，我希望它们从顶部列出，这样我就不必排序。我有一个电子表格示例，如果您想查看它，我可以发送电子邮件。非常感谢您提供的任何帮助！

I have a spreadsheet that I want to track people and their expiration dates. One of the sheets is everyone in my unit. I want another sheet to pull the names of individuals whose passports are expired or they don''t have one yet. I used this expression to pull the names =IF(OR(''Whole Squad''!B2="None", ''Whole Squad''!B2="exp"),''Whole Squad''!A2,"") but if I use this one it will put the names in the corresponding cell and I want them to be listed from the top so that I don''t have to sort. I have an example of the spreadsheet that I can email if you would like to view it. Thank you very much for any help you can provide!

推荐答案

我不明白你...如果你不想使用配方，为什么你询问哪个更好：宏观或公式？如果要从第二个工作簿/工作表的顶部枚举/列出员工，因为您不想对公式列上的数据进行排序，则需要使用VBA代码（宏）。



没什么难的！



请看这个msdn网站上的文章：在一系列单元格中循环[Excel 2003 VBA语言参考] [ ^ ]



示例：

I do not understand you... If you don''t want to use formula, why are you asking about which is better: macro or formula? If you want to enumerate / list employees from the top of the second workbook/worksheet, because you don''t want to sort data on formula column, you need to use VBA code (macro).

There is nothing hard to do!

Please, see this article on msdn site: Looping Through a Range of Cells [Excel 2003 VBA Language Reference][^]

Example:

Option Explicit

Sub EnumEmplExpPassDate()
'declare variables
Dim srcWbk As Workbook, dstWbk As Workbook 'src and dest workbooks
Dim srcWsh As Worksheet, dstWsh As Worksheet 'src and dest worksheets
Dim i As Integer, j As Integer 'iter.

On Error GoTo Err_EnumEmplExpPassDate
    
    Set srcWbk = ThisWorkbook 'refer to workbook in which is macro
    Set dstWbk = Workbooks.Add 'add new workbook
    Set srcWsh = srcWbk.Worksheets(1) 'refer to the source sheet with employees names
    'or
    'Set srcWsh = srcwbk.Worksheets("Whole Squad")
    Set dstWsh = dstWbk.Worksheets(1) 'refer to the destination sheet, where you want to list people with expired passpord


    i = 2 'start from row 2 in src sheet
    j = 2 'start from row 2 in dest. sheet
    
    Do While srcWsh.Range("A" & i) <> ""
        'check status
        If srcWsh.Range("B" & i) = "Non" Or srcWsh.Range("B" & i) = "Exp" Then
            srcWsh.Range("A" & i).Copy dstWsh.Range("A" & j) 'copy data
            'if you want to copy the range of cells
            'srcWsh.Range("A" & i & "C" & i).Copy dstWsh.Range("A" & j) 'copy data
            j = j + 1 'increase counter in dst sheet
        End Select
        i = i + 1 'increase counter in src sheet
    Loop

    
'exit code
Exit_EnumEmplExpPassDate:
    On Error Resume Next 'ignore errors
    'clean up ;)
    Set srcWbk = Nothing
    Set dstWbk = Nothing
    Set srcWsh = Nothing
    Set dstWsh = Nothing
    Exit Sub

'error handler
Err_EnumEmplExpPassDate:
    MsgBox Err.Number, vbExclamation, Err.Number
    Resume Exit_EnumEmplExpPassDate
End Sub

这篇关于哪一个更好，怎么做？公式还是宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！