从另一个范围的值定义范围 [英] Defining a range from values in another range

问题描述

我有一个excel文件的任务已经完成或没有，由一列或一列表示。最终我对不同列中的数据感兴趣，但是我想要设置代码，因此它忽略了完成任务的行。到目前为止，我已经定义了包含yes / no的列范围，但是我不知道在这个范围上运行哪个命令。我想我想根据C列中的值定义一个新的范围。

  Option Explicit 
 
 Sub Notify（）
 Dim Chk As Range 
 Dim ChkLRow As Long 
 Dim WS1 As Worksheet 
 
 On Error GoTo WhatWentWrong 
 
应用程序.ScreenUpdating = False 
 
' - >如果列C中的文本为是，则忽略（CountIF？）
' - >查找列中的最后一个单元格，将列C范围设置为Chk
 
设置WS1 = Sheets（2011）
 
带有WS1 
 ChkLRow = .Range （C& Rows.Count）.End（xlUp）.Row 
设置Chk = .Range（C1：C& ChkLRow）
结束
 
  - >否则检查日期在列H 
' - >从该日期开始计算天数
' - >消息框中的显示列表
重新输入：
 Application.ScreenUpdating = True 
 Application.DisplayAlerts = True 
退出子
 WhatWentWrong：
 MsgBox Err.Description 
简历重新输入
 Application.ScreenUpdating = True 
 End Sub 
  

简单地根据列C中的值简单地定义一个范围，而不是首先将列C定义为范围，然后重新定义它？

谢谢

解决方案

是列H具有任务到达的日期，并且我想显示从此到当前日期的计数。这些任务由A列中的4位数字代码标识。我设想一个消息框，说明xx天的任务'1234'未完成。 - Alistair Weir 1分钟前

这是你正在尝试的吗？添加Col I用于可视化目的。它没有任何意义。

  Option Explicit 
 
 Sub Notify（）
 Dim WS1作为工作表
 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 
 
设置WS1 = Sheets（2011）
 
与WS1 
 ChkLRow = .Range（C& Rows.Count） .End（xlUp）.Row 
 
'~~>在此设置您的相关范围
 Set Chk = .Range（A1：H& ChkLRow）
 
'~~>删除任何过滤器
 ActiveSheet.AutoFilterMode = False 
 
使用Chk 
'~~>过滤器，
 .AutoFilter字段：= 3，条件1：=否
'~~>偏移（排除头文件）
设置FltrdRange = .Offset（1，0）.SpecialCells（xlCellTypeVisible）
'~~>删除任何过滤器
 ActiveSheet.AutoFilterMode = False 
 
对于每个aCell在FltrdRange 
如果aCell.Column = 8和_ 
 Len（修剪（.Range（A & aCell.Row）.Value）） 0和_ 
 Len（Trim（aCell.Value））<> 0然后
 msg = msg& vbNewLine& _ 
任务& .Range（A& aCell.Row）.Value& _ 
未满& _ 
 DateDiff（d，aCell.Value，Date）& 天。 
结束如果
下一个
结束
结束
 
'~~>显示消息
 MsgBox msg 
重新输入：
 Application.ScreenUpdating = True 
退出子
 WhatWentWrong：
 MsgBox Err.Description 
恢复重新输入
 End Sub 
  

SNAPSHOT

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.

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?

Thanks

解决方案

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

Is this what you are trying? Added Col I for visualization purpose. It holds no significance otherwise.

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

这篇关于从另一个范围的值定义范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！