根据两列中的条件创建范围 [英] Create a range based on criteria in two columns
问题描述
这与我最近发布的另一个问题有关,@ Stavros Jon很好地帮助了我.
This relates to another question I recently posted, which @Stavros Jon kindly helped me with.
我正在尝试根据B列和C列中的条件创建一个命名范围.如果B列包含单词"OSI"并且C列包含单词"Language",我想创建一个范围.
I am trying to create a named range based on criteria in column B and column C. I want to create a range if column B contains the word "OSI" and column C contains the word "Language".
我曾经尝试编辑我以前的代码,但是我无法正确使用语法并在计数器行中遇到对象错误.
I have tried editing my previous code, but I cannot get the syntax right and get an object error with the counter line.
Sub another()
'Create Ranges:
Dim featuresRng As Range
Dim rng As Range
Dim sht As Worksheet
Dim counter As Long
Dim cell As Range
Set sht = ThisWorkbook.Worksheets("Features")
Set featuresRng = sht.Range(sht.Range("C1"), sht.Range("C" & sht.Rows.Count).End(xlUp)) 'dynamically set the range of features
Set featuresRng2 = sht.Range(sht.Range("B1"), sht.Range("B" & sht.Rows.Count).End(xlUp))
counter = 0 'this counter will help us avoid Union(Nothing, some range), which would give an error
For Each cell In featuresRng 'loop through the range of features
If featuresRng.cell.Value = "Language" And featuresRng2.cell.Value = "OSI" Then
counter = counter + 1
If counter = 1 Then
Set rng = sht.Range(cell.Offset(0, 1), cell.Offset(0, 3))
Else
Set rng = Union(rng, sht.Range(cell.Offset(0, 1), cell.Offset(0, 3))) 'build the range
End If
End If
Next cell
Debug.Print rng.Address
ThisWorkbook.Names.Add "OSILAng", rng
End Sub
如何编辑代码以包含这两个条件?
How can I edit my code to include these two criteria?
此外,有时我在B列中的文本将包含其他单元格中的单词,例如过滤器"和过滤器和搜索",我也希望从C列单元格中的完全相同的文本中确定我的范围,而不仅仅是包含"本文.
Also, sometimes my text in column B will contain words in other cells, like "Filter" and "Filter and Search", I am also looking to make my range from the EXACT text in column C cells, not just 'contains this text.
提前谢谢!
推荐答案
尝试一下
Sub another()
Dim featuresRng As Range, NewArr As Variant
Dim rng As Range
Dim sht As Worksheet
Dim sRng As String
Dim i As Long
Set sht = ThisWorkbook.Worksheets("Features")
Set featuresRng = sht.Range(sht.Range("B1"), sht.Range("C" & sht.Rows.Count).End(xlUp))
rngArray = featuresRng
ReDim NewArr(1 To 1)
y = 1
For i = 1 To UBound(rngArray)
If rngArray(i, 2) = "Language" And rngArray(i, 1) = "OSI" Then
ReDim Preserve NewArr(1 To y)
NewArr(y) = featuresRng.Rows(i).Offset(0, 3).Address
y = y + 1
End If
Next i
sRng = Join(NewArr, Application.DecimalSeparator)
ThisWorkbook.Names.Add "OSILAng", sht.Range(sRng)
End Sub
这篇关于根据两列中的条件创建范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!