VBA code to find cells with any value and replace with blank

Roballistic

New Member
Joined
Jan 14, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Code throwing error:

OpenBook.Sheets(1).Range("I27:I526").Copy 'Copies Medical Plan Selection Column
ThisWorkbook.Worksheets("SMARTTemplate").Range("J2").PasteSpecial xlPasteValues 'Pastes copied values to new sheet
ThisWorkbook.Worksheets("SMARTTemplate").Range("J2:J501").Replace What:="Waive", Replacement:="WAIVE" 'Replaces any value within the copied data having value of "Waive" to all capitals "WAIVE"
ThisWorkbook.Worksheets("SMARTTemplate").Range("J2:J501").Replace What:<>"Waive", Replacement:="""" 'Compile error received on <>. I need the code to to look across the range and where there is anything not labeled "WAIVE" to replace whatever is in those cells to "ENROLL". And also need the blanks skipped.

Any help or suggestions are appreciated.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    35.6 KB · Views: 6

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Please try this code

VBA Code:
Sub WaiveEnroll()
  Dim Cel As Range
  Dim Rng As Range
  Dim wRng As Range
  Dim eRng As Range
  Dim aStr As String
  Dim SMSht As Worksheet
  
  Set SMSht = ThisWorkbook.Worksheets("SMARTTemplate")
  OpenBook.Sheets(1).Range("I27:I526").Copy 'Copies Medical Plan Selection Column
  SMSht.Range("J2").PasteSpecial xlPasteValues 'Pastes copied values to new sheet
  
  Set Rng = SMSht.Range("J2:J501")
  For Each Cel In Rng
    aStr = Cel.Value
    If aStr <> "" Then
      If aStr = "Waive" Then
        If Not wRng Is Nothing Then
          Set wRng = Union(wRng, Cel)
        Else
          Set wRng = Cel
        End If
      Else
        If Not eRng Is Nothing Then
          Set eRng = Union(eRng, Cel)
        Else
          Set eRng = Cel
        End If
      End If
    End If
  Next Cel
  If Not wRng Is Nothing Then wRng.Value "WAIVE"
  If Not eRng Is Nothing Then eRng.Value "ENROLL"
      

End Sub
 
Upvote 0
Sub WaiveEnroll() Dim Cel As Range Dim Rng As Range Dim wRng As Range Dim eRng As Range Dim aStr As String Dim SMSht As Worksheet Set SMSht = ThisWorkbook.Worksheets("SMARTTemplate") OpenBook.Sheets(1).Range("I27:I526").Copy 'Copies Medical Plan Selection Column SMSht.Range("J2").PasteSpecial xlPasteValues 'Pastes copied values to new sheet Set Rng = SMSht.Range("J2:J501") For Each Cel In Rng aStr = Cel.Value If aStr <> "" Then If aStr = "Waive" Then If Not wRng Is Nothing Then Set wRng = Union(wRng, Cel) Else Set wRng = Cel End If Else If Not eRng Is Nothing Then Set eRng = Union(eRng, Cel) Else Set eRng = Cel End If End If End If Next Cel If Not wRng Is Nothing Then wRng.Value "WAIVE" If Not eRng Is Nothing Then eRng.Value "ENROLL" End Sub
Not quite working... getting the attached error. Any ideas?
 

Attachments

  • Capture3.PNG
    Capture3.PNG
    27.6 KB · Views: 9
Upvote 0
Not quite working... getting the attached error. Any ideas?
So I thought (my first mistake:unsure:) that maybe the Waive and Enroll needed to be in parenthesis, so changed that and then ran to only find the error seen in attached?

1695067178454.png
 
Upvote 0
Try this:
VBA Code:
If Not wRng Is Nothing Then wRng.Value = "WAIVE"
  If Not eRng Is Nothing Then eRng.Value  = "ENROLL"
 
Upvote 0
Sorry

VBA Code:
If Not wRng Is Nothing Then wRng.Value = "WAIVE"
  If Not eRng Is Nothing Then eRng.Value = "ENROLL"
 
Upvote 0
Solution

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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
Back
Top