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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
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
53,269
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

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