CODEWITHSUNDEEP
excelvba
Pvelez_3
Pvelez_3

Reputation: 115

Referencing a cells address and storing it in a Range object

I am iterating through a column and wanting to catch the cells that meet the string of text that I am searching for. My problem occurs when I try and set the address of the cell that has met the criteria of the search into a Range object. Line: Set testRng = i.Range.Adress Gives me error " Wrong number of arguments or invalid property assignment" and I'm not sure what the issue is here?

This is the entire code I am working with:

Sub Tester()

Dim rng As Range
Dim testRng As Range
Dim i As Variant
Dim cmpr As String
Dim usrInputA As String
usrInputA = InputBox("Col 1 Criteria: ")
Set rng = Range("A2:A10")
For Each i In rng
    cmpr = i.Value
    If InStr(cmpr, usrInputA) Then
        If testRng Is Nothing Then
           Set testRng = i.Range.Address
        Else
            Set testRng = testRng & "," & i.Range.Address
        End If
    Else
        MsgBox "No hit"
    End If
    Next
End Sub

Upvotes: 2

Views: 46

Answers (1)

urdearboy
urdearboy

Reputation: 14580

  1. You should declare i as a Range (not Variant)
  2. Use Union to group together a collection of cells instead of trying to manually build the string yourself
  3. Switch the order of your range set. You only need Set rngTest = i once so I would put this at the bottom so you don't have to keep spamming it.
Option Explicit

Sub Tester()

Dim testRng As Range, i As Range
Dim usrInputA As String
Dim LR as Long

usrInputA = InputBox("Col 1 Criteria: ")
LR = Range("A" & Rows.Count).End(xlUp).Row

For Each i In Range("A2:A" & LR)
    If InStr(i, usrInputA) Then
        If Not testRng Is Nothing Then
           Set testRng = Union(testRng, i)
        Else
           Set testRng = i
        End If
    End If
Next i

If Not testRng is Nothing Then
    'Do what with testRng?
End If

End Sub

Upvotes: 2

Related Questions