Adding to a Range

[AlexanderBB]

                        If lstYr > "" Then
                                Set rtracks = .Range("A1:S1").Find(What:="*" & Right(lstYr, 2) & "*", LookIn:=xlValues, LookAt:=xlPart)
                                For Each Found In rtracks
                                Debug.Print Found
                                Next
                                Stop
                        Else
                                Set rtracks = .Range("A2:S2")
                                For Each Found In rtracks
                                Debug.Print Found
                                Next
                                Stop
                        End If

if lstYr is "" rtracks contains all Founds in that range. But otherwise it only has the first instance.
Because the way rtracks is used, I need it to contain every instance. How might I do that ?

 

[Joe4]

Can you show us an image of your data, and explain exactly what it is you are trying to do?
It would also be helpful if you showed us your complete VBA procedure, so we can where these variables are coming from and what they are being set to.

 

[HighAndWilder]

                        If lstYr > "" Then
                                Set rtracks = .Range("A1:S1").Find(What:="*" & Right(lstYr, 2) & "*", LookIn:=xlValues, LookAt:=xlPart)
                                For Each Found In rtracks
                                Debug.Print Found
                                Next
                                Stop
                        Else
                                Set rtracks = .Range("A2:S2")
                                For Each Found In rtracks
                                Debug.Print Found
                                Next
                                Stop
                        End If

if lstYr is "" rtracks contains all Founds in that range. But otherwise it only has the first instance.
Because the way rtracks is used, I need it to contain every instance. How might I do that ?

The function 'fncFindInstancesInRange' will return a range of cells, each of which contains the value searched for.

I have added a calling sub and some worksheet data for you to test it.

Can you use this for your project?

Adding to a Range.xlsm
	A	B	C
1	Column 1	Column 2	Column 3
2	GA3NA5GU^	xN^#	0Cf
3	i62P@HPKax	yJLY3e*V#	GU^%xf^bs
4	rxN^#	%xf^bs@m!r	4KGl%elgw
5	Am#k*	zYk	2i49$1R
6	4KGl%elgw0	O$Bk	hu70Wr&Zzr
7	X6Xme3g	Ddoa&!!	yad5Zfy
8	Cfhu70Wr&	BQIhc7T0	dCOPMT*7*
9	c7T0pkzE	zHSDuoo	wh8vj2&ss
10	ZzrdCOPM	pkzEY1lxL	o54INR
11	@p3	lipCMV!	bb#FW8
12	T*7*o54	EsuUv	Gn8ycEH
13	Y1lxL	mtZir%gf0	4cGA3NA5
14	INRG	2$zYkl	jDJcq
15	@6Eb	KJejnc9MC	uWoFFp1
16	n8y	ipC	ASB1
17	EsuUv2$	8^vlvS	Q6Vt9tq
18	cEHjDJcqA	MV!KJejnc	TxwQ9O
19	u57Bha%&q	nrf$iFD	@m!
20	SB1TxwQ9	9MCy	$BkBQIh
Sheet1

Cell Formulas
Range	Formula
A1:C1	A1	="Column " & COLUMN()

Public Sub subAddingToARange()
Dim rng As Range
    
    Set rng = fncFindInstancesInRange(Worksheets("Sheet1").Range("A2:C20"), 1)
             
    MsgBox rng.Address

End Sub

Public Function fncFindInstancesInRange(SearchRange As Range, SearchWord As String) As Range
Dim Position As Long
Dim cell As Range
Dim rng As Range

  For Each cell In SearchRange
  
      Position = InStr(1, cell, SearchWord, vbTextCompare)
    
      Do While Position > 0
      
        If StrComp(SearchWord, Mid(cell.Value, Position, Len(SearchWord))) = 0 Then
  
            If Not rng Is Nothing Then
            
              Set rng = Union(rng, cell)
              
            Else
              
              Set rng = cell
              
            End If
        
        End If
       
        Position = InStr(Position + 1, cell, SearchWord, vbTextCompare)
      
      Loop
      
  Next cell
    
  Set fncFindInstancesInRange = rng
  
End Function

 

[AlexanderBB]

Joe, what I posted was an example of a particular task in isolation. Without any more code.
Someone told me a range didn't have to be continuous, but if it was ""I:K" then column J is included.. But I just found that "I,K"
will only include those 2 columns. @HighAndWilder, your code worked perfectly with rng.Address correctly being $I$1,$K$1
I ended up Looping through the range and building a string of column Letters, coma delimited. Your method is more elegant so I've changed to that.
Thanks, nice solution.

 

[HighAndWilder]

Joe, what I posted was an example of a particular task in isolation. Without any more code.
Someone told me a range didn't have to be continuous, but if it was ""I:K" then column J is included.. But I just found that "I,K"
will only include those 2 columns. @HighAndWilder, your code worked perfectly with rng.Address correctly being $I$1,$K$1
I ended up Looping through the range and building a string of column Letters, coma delimited. Your method is more elegant so I've changed to that.
Thanks, nice solution.

I'm glad that it worked for you.

Have a read of this about non-contigious ranges, Ask any questions if you have any.

Range Areas: Working with Non Contiguous Ranges Using Range Areas in VBA - FasterCapital