VBA: Get Range of found cell

[thes4s]

Hi everyone. I have this code that finds a value in the cells of a defined range. I need help on how to set a new range of the cell that is found. The new range should be everything below the found cell to the last row used.

Also I am not sure if there is a better way, but with the current code, it will find "Test" in the entire range. In a realistic setting, Test should only be found once in the entire range.

Here is my code so far:

Sub test()
Dim rng As Range, newRng As Range

Set rng = Range("A2:ZZ2")
    
    For Each cell In rng.Cells
        If cell.Value = "Test" Then
          Set newRng = 'SOME CODE
        End If
    Next
    
End Sub

[Paul_Hossler]

If I understand correctly, I think this is all you need

Set newRng = cell
Exit For

If you find "Test" then there's no need to continue inside the For loop

[thes4s]

Thanks for your reply.. let me try this. Not sure if cell is the exact range I was asking for.

[Kenneth Hobs]

Welcome to the forum!

If cell was not what you wanted, please explain more.

Have you considered Range's Find method?

Option Explicit
'Option Compare Binary 'Case sensitive comparisons.
Option Compare Text   'Case insensitive comparisons.


Sub paul()
  Dim rng As Range, newRng As Range
  Dim cell As Range
   
  Set rng = Range("A2:ZZ2")
   
  For Each cell In rng
    If cell.Value2 = "Test" Then
      Set newRng = cell 'SOME CODE
      Exit For
    End If
  Next
  
  If Not cell Is Nothing Then MsgBox "Test found in cell: " & cell.Address
End Sub


Sub ken()
  Dim newRng As Range, r As Range
  
  Set r = Range("A2:ZZ2")
   
  Set newRng = r.Find("Test", after:=r(r.Cells.Count), SearchOrder:=xlNext)
  If Not newRng Is Nothing Then _
    MsgBox "Found case insensitive ""Test"" in cell: " & newRng.Address
  
  Set newRng = r.Find("Test", after:=r(r.Cells.Count), _
    SearchOrder:=xlNext, MatchCase:=True)
  If Not newRng Is Nothing Then _
    MsgBox "Found case sensitive ""Test"" in cell: " & newRng.Address
End Sub

[Paul_Hossler]

Thanks for your reply.. let me try this. Not sure if cell is the exact range I was asking for.

That's always possible, but that's what the code was asking for

As Ken says, more information might be helpful if it's not

[snb]

Sub M_snb() 
  msgbox  Range("A2:ZZ2").find("Test").offset(1).resize(usedrange.rows.count-2).address
End Sub

[SamT]

Sub Test()
'Sets NewRng = Used Range below first instance of "Test".
'Does not include Row of "Test"
'Does include entire last Column even if last rows of column are empty

Dim Found As Range
Dim NewRng As Range

With Sheet1.UsedRange
Set Found = .Find("Test")
If Not Found Is Nothing Then _
  Set NewRng = Range(Cells(Found.Row + 1, 1), .Cells(.Cells.Count))
End With
End Sub

[snb]

@SamT


Why ignoring the object variable you just created ?

If Not Found Is Nothing Then Set NewRng = range(found.offset(1),found.end(xldown))

[SamT]

To make clear to beginner what is going on.

In production I could replace all "Found" with "NewRng."

It is not clear (to me) if the OP wants "NewRng" to be just the Column of "Test" or the entire Used Range below "Test."

[snb]

To me neither.

[thes4s]

Sub Test()
'Sets NewRng = Used Range below first instance of "Test".
'Does not include Row of "Test"
'Does include entire last Column even if last rows of column are empty

Dim Found As Range
Dim NewRng As Range

With Sheet1.UsedRange
Set Found = .Find("Test")
If Not Found Is Nothing Then _
  Set NewRng = Range(Cells(Found.Row + 1, 1), .Cells(.Cells.Count))
End With
End Sub

Thx for this. I will try this and will keep you up to date. But from the looks of the code, from my limited knowledge of VBA. Once "test" is found, whenever it is at, it will be set as the range correct? The reason I ask, is the range set with the horizontal or vertical range?

[SamT]

A worksheet's "UsedRange" is defined as a rectangle that contains every used cell on the sheet. A Used cell is defined as a cell the has a formula or value or is formatted.

That sub sets "NewRng " = the entire UsedRange that is below the Row that has the first instance of "Test" in it.

Paul's post # 2 And Kenneth's post 34 sets NewRng = the cell that has "Test" in it.

You did not specify what you wanted NewRng set to. you just said the Range below Test.

This part of my sub is using VBA shorthand

Set NewRng = Range(Cells(Found.Row + 1, 1), .Cells(.Cells.Count))

Set NewRng = Range(Range1,Range2) sets NewRng to all the Range between and including Range1 and Range2

A Range can be addressed by Cells(Row, Column)
Cells(Found.Row +1, 1) is the First Column in the Row below (Row + 1) Found (the "Test" cell)

Since it is within the With ...End With block, the bare dots before ".Cells" is shorthand for

Set NewRng = Range(Cells(Found.Row + 1, 1), Sheet1.UsedRange.Cells(Sheet1.UsedRange.Cells.Count))

If the UsedRange has n Cells, then .Cells(n) is the last cell in the Used Range

[thes4s]

You did not specify what you wanted NewRng set to. you just said the Range below Test.

SamT, I am sorry if I did not specify the new range. But let me specify, once "Test" is found for example if it is found on B2. The first cell below it to the last cell used in the column will be the new range. So newRng = Range("B3:B17"). Does your code you provided do this for me? Thanks again.

[SamT]

No it doesn't.

Why don't you try it first and let us see what you come up with