Set a new range after searching for a value

[jgeller]

I am searching my entire worksheet for an account number, once I find that account number, I need to search again within a new range. The new range starts with the first cell i searched for (account number) and ends 45 rows down. How can I resize the range after I already searched? Thanks

 

[Norie]

Can you post the code you have?

Generally to resize a range you would, surrpisingly, use Resize.

Set rng = Range("A1")

Set rng = rng.Resize(45) ' resize rng by 45 rows, rng now refers to AA1:A45

 

[jgeller]

Sub Macro()


Dim accountNumber As String




'USER INPUTS'
accountNumber = InputBox(Prompt:="What is the account number", _
          Title:="Account Name", Default:="ex: 3192-003")






'Search'
Sheets("Sheet1").Select 
Range("b1:b36675").Find(accountNumber).Select 'Search for  acc number

End Sub

Now with the selected cell, how can i search a new range from the selected cell and 45 rows down? Thanks

basically I want to search again, but this time instead of from "b1" it should be the new selected cell, and "b36675" should be "b1+45".

 

[Norie]

Perhaps something like this.

Sub Macro()
Dim rngFnd As Range
Dim accountNumber As String

    'USER INPUTS'
    accountNumber = InputBox(Prompt:="What is the account number", _
                             Title:="Account Name", Default:="ex: 3192-003")
                             
    Set rngFnd = Sheets("Sheet1").Range("b1:b36675").Find(accountNumber)    'Search for  acc number

    If rngFnd Is Nothing Then
        MsgBox "Account number " & accountNumber & " not found."
    Else
        Set rngFnd = rngFnd.Resize(45).Find("Something")

        If Not rngFnd Is Nothing Then
            MsgBox "Something found in " & rngFnd.Address
        End If
    End If

End Sub

 

[jgeller]

That works great. Thank you.