Trouble getting a range value to return as not nothing - VBA

Boswell14

New Member
Joined
Nov 12, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I am trying to get the code to search for the replacement criteria and then loop a replacement sub until there are no more replacement criteria. The main reason I am doing it like this is the active cell reference is important for what the criteria is being replaced with. The main issue is my "acell" range is not finding anything and being set as nothing even though the criteria is in the sheet and I can't tell why (or I am not understanding the function correctly). If anything else looks off any help would be appreciated here.

VBA Code:
Sub looptest()

'trying to have the relpace sequence one cell at a time

Dim acell As Range
Dim cellAddress As Range


    With Sheets("Template").Range("AA:Z")
        
        Set acell = .Find(What:="Replace", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
        If Not acell Is Nothing Then
            cellAddress = acell.Address
            Do
            'Have it select the cell it found the "replace" on and then call replacement sub then loop the process until no more replacement criteria are found
            
            cellAddress.Select
            replacement
            Set acell = .FindNext(acell)
            Loop While Not acell Is Nothing
        End If
    End With

End Sub

VBA Code:
Sub replacement()

'houses the logic to run the replacement sequence

    Dim strRef As String
    Dim LD As String

    Let strRef = ActiveCell.Address
    Let LD = Workbooks("test.xlsm").Sheets("test sheet").Range("M45")


    Selection.Replace What:="Replace", replacement:="='[test.xlsm]MSRP'!" & strRef & "*(1-" & LD & ")", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

End Sub
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What column(s) hold the word Replace?
 

Boswell14

New Member
Joined
Nov 12, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Had to change a line or two but it works now. Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,863
Messages
5,627,332
Members
416,241
Latest member
moggy84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top