vba 从另一个范围中的值定义一个范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10322726/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Defining a range from values in another range
提问by Alistair Weir
I have an excel file of tasks which have either been completed or not, indicated by a Yes or No in a column. Ultimately I am interested in data in a different column but I want to set up the code so it ignores those rows where the task has been completed. So far I have defined the column range containing the yes/no's but I don't know which command to run on this range. I imagine I want to define a new range based on the value in column C.
我有一个已完成或未完成的任务的 excel 文件，在列中用“是”或“否”表示。最终，我对不同列中的数据感兴趣，但我想设置代码，以便它忽略任务已完成的那些行。到目前为止，我已经定义了包含是/否的列范围，但我不知道在这个范围内运行哪个命令。我想我想根据 C 列中的值定义一个新范围。
Option Explicit
Sub Notify()
Dim Chk As Range
Dim ChkLRow As Long
Dim WS1 As Worksheet
On Error GoTo WhatWentWrong
Application.ScreenUpdating = False
'--> If the text in column C is Yes then Ignore (CountIF ?)
'--> Find last cell in the column, set column C range as "Chk"
Set WS1 = Sheets("2011")
With WS1
ChkLRow = .Range("C" & Rows.Count).End(xlUp).Row
Set Chk = .Range("C1:C" & ChkLRow)
End With
'--> Else Check date in column H
'--> Count days from that date until today
'--> Display list in Message Box
Reenter:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub
WhatWentWrong:
MsgBox Err.Description
Resume Reenter
Application.ScreenUpdating = True
End Sub
Would it perhaps be easier to simply define one range based on the values in column C rather than first defining column C as the range and then redefining it?
根据 C 列中的值简单地定义一个范围，而不是先将 C 列定义为范围，然后重新定义它，是否会更容易？
Thanks
谢谢
回答by Siddharth Rout
Yes Column H has the date the task 'arrived' and I want to display a count from then to the current date. The tasks are identified by a 4 digit code in Column A. I envisage the message box saying Task '1234' outstanding for xx days. – Alistair Weir 1 min ago
是的，H 列有任务“到达”的日期，我想显示从那时到当前日期的计数。任务由 A 列中的 4 位代码标识。我设想消息框显示 xx 天未完成的任务“1234”。– Alistair Weir 1 分钟前
Is this what you are trying? Added Col I for visualization purpose. It holds no significance otherwise.
这是你正在尝试的吗？添加 Col I 用于可视化目的。否则它没有任何意义。
Option Explicit
Sub Notify()
Dim WS1 As Worksheet
Dim Chk As Range, FltrdRange As Range, aCell As Range
Dim ChkLRow As Long
Dim msg As String
On Error GoTo WhatWentWrong
Application.ScreenUpdating = False
Set WS1 = Sheets("2011")
With WS1
ChkLRow = .Range("C" & Rows.Count).End(xlUp).Row
'~~> Set your relevant range here
Set Chk = .Range("A1:H" & ChkLRow)
'~~> Remove any filters
ActiveSheet.AutoFilterMode = False
With Chk
'~~> Filter,
.AutoFilter Field:=3, Criteria1:="NO"
'~~> Offset(to exclude headers)
Set FltrdRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
'~~> Remove any filters
ActiveSheet.AutoFilterMode = False
For Each aCell In FltrdRange
If aCell.Column = 8 And _
Len(Trim(.Range("A" & aCell.Row).Value)) <> 0 And _
Len(Trim(aCell.Value)) <> 0 Then
msg = msg & vbNewLine & _
"Task " & .Range("A" & aCell.Row).Value & _
" outstanding for " & _
DateDiff("d", aCell.Value, Date) & "days."
End If
Next
End With
End With
'~~> Show message
MsgBox msg
Reenter:
Application.ScreenUpdating = True
Exit Sub
WhatWentWrong:
MsgBox Err.Description
Resume Reenter
End Sub
SNAPSHOT
快照
回答by ja72
Why not brute force it.
为什么不蛮力呢。
Dim r_table as Range, i as Integer, N as Integer
' Start from the top
Set r_table = Sheets("2011").Range("C1")
' Find the last entry on column C and count the # of cells
N = Sheets("2011").Range(r_table, r_table.End(xlDown)).Rows.Count
Dim table_values() as Variant
' This will transfer all the values from the spreadsheet into an VBA array
' and it works super fast. Access values with A(row,col) notation.
table_values = r_table.Resize(N, 5).Value2 ' No. of columns is 5 ?
For i=1 to N
If table_values(i,1)="Yes" Then 'Check Column C
Else
... table_values(i,5) ' Column H
End if
Next i
MsgBox ....
This will be super fast, with no flicker on the screen.
这将是超快的，屏幕上没有闪烁。
