VBA检查范围内的值 [英] VBA check for value in a range

查看:169
本文介绍了VBA检查范围内的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图循环一个列,如果cell =我在看什么,然后做一些事情。
到目前为止,我已经在我的if语句中查找name:

I am trying to loop through a column and if cells = "what i'm lookng for" then do something. I have this so far, where I'm off is in the if statement where I check for the "name":

Option Explicit

Sub test()

Dim wksDest             As Worksheet
Dim wksSource           As Worksheet

Dim rngSource           As Range

Dim name                 As String

Dim LastRow             As Long
Dim LastCol             As Long
Dim c                   As Long

Application.ScreenUpdating = False

Set wksSource = Worksheets("Sheet1")

With wksSource
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For c = 16 To 20
LastRow = .Cells(.Rows.Count, c).End(xlUp).Row
Set rngSource = .Range(.Cells(5, 16), .Cells(LastRow, 16))
name = rngSource.Value

If name = "mark"
do something 
End If

Next c
End With

Application.ScreenUpdating = True

'MsgBox "Done!", vbExclamation

End Sub


推荐答案

OK Chris
也许有点需要简化,但也有一些假设。
似乎没有LastCol被用于任何东西 - 所以让我们假设这是你要循环的列。
你的循环有固定的开始和结束值,但你确定LastRow - 所以让我们假设你想从第5行开始(在代码中)并循环到LastCol中的LastRow。
为了确定LastCol,您必须在所使用的行中具有数据来执行此操作 - 所以让我们假设在要循环的所有列中的第1行中有值为16(在代码中) 。
如果在这种情况下想要(IF)测试单个(字符串)值,则必须安排您的rngSource为单个单元格值。您也不需要将其分配给变量,除非您需要再次使用它。
最后,如果要检查其他值,您可能需要考虑使用SELECT CASE结构代替IF THEN结构。
查看以下内容并更改我的假设以满足您的要求 - 祝你好运。

OK Chris Maybe a bit of simplification is required but also a few assumptions. It doesn't seem like LastCol is being used for anything - so let's assume this is the Column you want to loop through. Your loop has fixed start and end values yet you are determining the LastRow - so let's assume you want to start from row 5 (in your code) and loop to the LastRow in the LastCol. In order to determine LastCol you must have data in the row you are using to do this - so let's assume that there are values in row 1 in all columns up to column you want to loop say 16 (in your code). If you want to (IF) test for a single (string) value in this case then you must arrange for your rngSource to be a single cell value. You also don't need to assign this to a variable unless you need to use it again. Finally, if you want to check for other values you may want to consider using a SELECT CASE structure in place of your IF THEN structure. Have a look at the following and change my assumptions to meet your requirement - good luck.

Sub test()

Dim wksDest             As Worksheet
Dim wksSource           As Worksheet

Dim rngSource           As Range

Dim name                 As String

Dim LastRow             As Long
Dim LastCol             As Long
Dim c                   As Long

Application.ScreenUpdating = False

Set wksSource = Worksheets("Sheet1")

    With wksSource
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(Rows.Count, LastCol).End(xlUp).Row

        FirstRow = 5
            For c = FirstRow To LastRow
                If .Range(.Cells(c, LastCol), .Cells(c, LastCol)).Value = "Mark" Then
                    MsgBox ("do something")
                End If
            Next c
    End With

End Sub

这篇关于VBA检查范围内的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆