VBA检查范围内的值 [英] VBA check for value in a range
问题描述
我试图循环一个列,如果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屋!