VBA Userform - Find & Replace

[Robby19]

Found this code to Find and Replace;

[COLOR=#00007f]Sub[/COLOR] FindReplaceAll()
[COLOR=#007f00]'PURPOSE: Find & Replace text/values throughout entire workbook[/COLOR]
[COLOR=#007f00]'SOURCE: www.TheSpreadsheetGuru.com[/COLOR]

[COLOR=#00007f]Dim[/COLOR] sht [COLOR=#00007f]As[/COLOR] Worksheet
[COLOR=#00007f]Dim[/COLOR] fnd [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Variant[/COLOR]
[COLOR=#00007f]Dim[/COLOR] rplc [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Variant[/COLOR]

fnd = "April"
rplc = "May"

[COLOR=#00007f]For[/COLOR] [COLOR=#00007f]Each[/COLOR] sht [COLOR=#00007f]In[/COLOR] ActiveWorkbook.Worksheets
  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=[COLOR=#00007f]False[/COLOR], ReplaceFormat:=False
[COLOR=#00007f]Next[/COLOR] sht

[COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR]

How would I change it so that it is only looking inside a certain column within a table rather than the worksheet as a whole?

Also I would need help adding on to this. For example, if optbtnAll was checked, it would replace all (either C or V) to R. It optbtnCleared was checked, it would only replace the letters in column [Status] from C to R. And if optbtnVirtual was checked it would replace the letters in column [Status] from V to R.

 

[My Aswer Is This]

Please give us the Table Name and what you want to do within the table.
Replace what with what.

 

[My Aswer Is This]

Assuming your Table is named "Sam"
Modify Sam to your Table name.

Try this:

Sub Table_Change()
'Modified 6/12/18 12:05 AM EDT
Dim c As Range
Dim fnd As Variant
Dim rplc As Variant
fnd = "April"
rplc = "May"
For Each c In ActiveSheet.ListObjects("Sam").Range.Columns(1)
  c.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next

 

[My Aswer Is This]

Not sure this is what you want. You mention a UserForm and then a Table.

 

[Robby19]

In a nutshell
1. User clicks a button and opens a userform
2. User chooses to reconcile 1 of 3 options; All, Cleared, Virtual
3. User types in a begin and end date that they would like to reconcile the data for. Click Save
4. The data in table AccountRegister, column name Status gets checked and changed as appropriate.

For example, if the User chooses Cleared, and between 1/1/2018 - 12/31/2018. The VBA will check the table and column and Replace all cells that say Cleared in column Status, and replace with Reconciled.

 

[My Aswer Is This]

Looks like you want a lot more then your original script you showed attempts to do.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Robby19]

This selects the right criteria for one argument, however it breaks excel. Any suggestions?

If optbtnCleared.Value = True Then
    Range("AccountRegister[Status]").Select
    Selection.Replace What:="C", Replacement:="R", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End If

 

[dmt32]

In a nutshell
1. User clicks a button and opens a userform
2. User chooses to reconcile 1 of 3 options; All, Cleared, Virtual
3. User types in a begin and end date that they would like to reconcile the data for. Click Save
4. The data in table AccountRegister, column name Status gets checked and changed as appropriate.

For example, if the User chooses Cleared, and between 1/1/2018 - 12/31/2018. The VBA will check the table and column and Replace all cells that say Cleared in column Status, and replace with Reconciled.

Hi,
Based on your requirement you should be able to Autofilter between the required Dates & Status fields & then change visible status cells to “R”

Without any data or proper understanding of the worksheet layout, I have not really been able to test the following suggestion but see if it helps you
Note: Code you first showed cycled through all worksheets -I am guessing that data is just on one worksheet?


Place following in your forms code page

 Private Sub CommandButton1_Click()    
Reconcile Form:=Me, StartDate:=DateValue(Me.TextBox1.Text), EndDate:=DateValue(Me.TextBox2.Text)
End Sub


Private Sub optbtnAll_Click()
    Me.Tag = 1
End Sub


Private Sub optbtnCleared_Click()
    Me.Tag = 2
End Sub


Private Sub optbtnVirtual_Click()
    Me.Tag = 3
End Sub

Place following either in Standard module or your forms code page

 Sub Reconcile(ByVal Form As Object, ByVal StartDate As Long, ByVal EndDate As Long)

    Dim ws As Worksheet
    Dim FilterStatus As Variant, Item As Variant
    Dim ReplaceWith As String
    Dim StatusColumn As Long, DateColumn As Long, FilterCount As Long
    Dim Action As Integer
    Dim rng As Range, cell As Range
   
    
    Action = Val(Form.Tag)
    If Action = 0 Then Exit Sub
    
'************************************************************************************************************
'*************************************************SETTINGS***************************************************
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    StatusColumn = 1
    DateColumn = 2
    FilterStatus = Choose(Action, "", "C", "V")
    ReplaceWith = "R"
'************************************************************************************************************
'clear autofilter
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    With ws.Range("A1").CurrentRegion
'Autofilter data between Dates
        .AutoFilter DateColumn, ">=" & StartDate, xlAnd, "<=" & EndDate
'Autofilter Status Column
     If Action > 1 Then .AutoFilter StatusColumn, FilterStatus
    End With
'autofilter range
    Set rng = ws.AutoFilter.Range
'ignore header row
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
    
    With rng.Columns(StatusColumn).SpecialCells(xlCellTypeVisible)
'check filter count and update record(s)
        If .Count > 0 Then .Value = ReplaceWith
    End With
'reset
   rng.AutoFilter
   
End Sub

You will need to update the values shown in RED as required to match name of worksheet & columns your data resides & the control (textbox) names you enter dates.

I am not around much for today but hopefully, suggestion will give you something you can develop.

ALWAYS MAKE A BACKUP BEFORE TESTING NEW CODE.

Dave

 

[Robby19]

I appreciate the responses from all. After much "finagling" and with the help of another, we came down to this;

Private Sub btnSave_Click()
    Dim oTbl As ListObject
    Dim rCl As Range
    Const strReplace As String = "R"
    Dim strFrom As String
    'Error if Date not formatted correctly
    If Not IsDate(Me.txtStartDate.Text) Then
        MsgBox "The Data entered in Start Date is not a Date." & Chr(10) & "Please re-format it as appropriate."
        Exit Sub
    End If
    'Error if Date not formatted correctly
    If Not IsDate(Me.TxtEndDate.Text) Then
        MsgBox "The Data entered in End Date is not a Date." & Chr(10) & "Please re-format it as appropriate."
        Exit Sub
    End If
    With Sheet3

        Set oTbl = .ListObjects(1)
        
        .Unprotect
        
        Select Case True
        Case Me.optbtnCleared
        
            strFrom = "C"
        Case Me.optbtnVirtual
            strFrom = "V"
            
        Case Me.optbtnAll
            For Each rCl In oTbl.ListColumns(3).Range
                If rCl.Value >= CDate(Me.txtStartDate) And rCl.Value <= CDate(Me.TxtEndDate) Then
                    If Not rCl.Offset(, 6) = strReplace Then rCl.Offset(, 6) = strReplace
                End If
            Next rCl
        End Select
        For Each rCl In oTbl.ListColumns(3).Range
            If rCl.Value >= CDate(Me.txtStartDate) And rCl.Value <= CDate(Me.TxtEndDate) Then
                If rCl.Offset(, 6) = strFrom Then rCl.Offset(, 6) = strReplace
            End If
        Next rCl
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True
    End With
    'Close userform
    Unload Me
End Sub