如何查找具有多个条件的单元格 [英] How to find a cell with multiple criteria

问题描述

您好 

我正在使用Microsoft Excel 2010和VBA编程的新功能。 

I am using Microsoft excel 2010 and new to programming with VBA. 

我有一个很大的Excel包含49张包含6列和23393行的文件。我想在每个工作表中找到一个具有多个条件的单元格，选择并获取要在公式中使用的单元格（作为参考）的地址。我写了一个代码并且它执行了很好的
，但这个大文件太慢了。这是代码：

I have a large excel file that contains 49 sheets which contain 6 columns and 23393 rows. I want to find a cell with multiple criteria in each sheets , select and get address of cell(as reference) to use in a formula. I wrote a code and it performs good but too slow for this large file. here is the code :

Sub RegionalAverage()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.Calculation = xlCalculationManual

'get the number of rows in sheet 
'
Range("A1").Select
numrow = Range(Selection, Selection.End(xlDown)).Count

'create worksheets for result
'
ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "Output"

n=32
date_ini = #1/1/2008#
date_fin = #12/31/2009#
Dim Address(49) As String
For j = 1 To n
For conteo = date_ini To date_fin
For i = 1 To 49

'going to each sheet to filter and offset to find specific cell
'
Sheets(i).Activate
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("A1:H" & numrow & "").AutoFilter Field:=6, Criteria1:=j
Columns("A:H").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Find(What:=conteo, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(0, 4).Select

'getting complete address of mentioned cell
'
Address(i) = "'" & Selection.Parent.Name & "'" & "!" & Selection.Address(External:=False)
Selection.AutoFilter
Next i

'computation section in output sheet
'
area = 6.429571
Sheets("Output").Activate
Rows(1).Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
colname = Split(ActiveCell(1).Address(1, 0), "$")(0)
Columns("" & colname & ":" & colname & "").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
'the formula
'
ActiveCell.Value = "=(SUM(" & Address(1) & "," & Address(2) & "," & Address(3) & "," & Address(4) & "," & Address(5) & "," & Address(6) & "," & Address(7) & "," & Address(8) & "," & Address(9) & "," & Address(10) & "," & Address(11) & "," & Address(12) & "," & Address(13) & "," & Address(14) & "," & Address(15) & "," & Address(16) & "," & Address(17) & "," & Address(18) & "," & Address(19) & "," & Address(20) & "," & Address(21) & "," & Address(22) & "," & Address(23) & "," & Address(24) & "," & Address(25) & "," & Address(26) & "," & Address(27) & "," & Address(28) & "," & Address(29) & "," & Address(30) & "," & Address(31) & "," & Address(32) & "," & Address(33) & "," & Address(34) & "," & Address(35) & "," & Address(36) & "," & Address(37) & "," & Address(38) & "," & Address(39) & "," & Address(40) & "," & Address(41) & "," & Address(42) & "," & Address(43) & "," & Address(44) & "," & Address(45) & "," & Address(46) & "," & Address(47) & "," & Address(48) & "," & Address(49) & "))/" & area & ""

Next conteo
Next j

End Sub

请你建议一个快速的方式 和帮助我达到目的！？

Would you please suggest a quick way  and help me to reach purpose !?

谢谢

推荐答案

Hi Majid Javanmard，



根据您的描述，您可以关注
Excel VBA性能编码最佳实践，以优化Excel VBA性能。



另外我建议您可以在OneDrive上传您的Excel文件，这将有助于我们重现并解决您的问题。



感谢您的理解。

Hi Majid Javanmard,

According to your description, you could follow this article about Excel VBA Performance Coding Best Practices to optimize Excel VBA performance.

In addition I suggest that you could upload your Excel file on OneDrive, that will help us reproduce and resolve your issue.

Thanks for your understanding.

这篇关于如何查找具有多个条件的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！