VBA inadvertently disabling Find and Replace

mjhopler

New Member
Joined
Jun 1, 2010
Messages
7
I have written the below VBA. What it is trying to do: It pulls data from another sheet copies and pastes it, does a find and replace and then uploads the data to a database.

The problem: something in the code is turning off the Find and Replace function, so once it gets to this point, it gives an error. Once I close the macro and try a regular find and replace, no matter what I try to find, it says it cannot find it. Does anyone see anything in this code that would cause the find and replace to no longer work?

Code:
Sub RRUpload()
Dim cnt As New ADODB.Connection, _
            rst As New ADODB.Recordset, _
            dbPath As String, _
            tblName As String, _
            rngColHeads As Range, _
            rngTblRcds As Range, _
            colHead As String, _
            rcdDetail As String, _
            ch As Integer, _
            cl As Integer, _
            notNull As Boolean
            Dim ws As Worksheet
            Set ws = Worksheets("RentRollUpload")
            Set ms = Worksheets("DB")
            
              
    'Set the string to the path of the database as defined on the worksheet
    dbPath = ms.Range("A50")
    tblName = ms.Range("A57")
    Set rngColHeads = ws.Range("RRhdrs")
    Set rngTblRcds = ws.Range("RRdat")

    'Concatenate a string with the names of the column headings
    colHead = " ("
    For ch = 1 To rngColHeads.Count
        colHead = colHead & rngColHeads.Columns(ch).Value
        Select Case ch
            Case Is = rngColHeads.Count
                colHead = colHead & ")"
            Case Else
                colHead = colHead & ","
        End Select
    Next ch

    'Open connection to the database
    cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & dbPath & ";"
    
    
    'Begin transaction processing
    'On Error GoTo EndUpdate
    cnt.BeginTrans

    'Insert records into database from worksheet table
    For cl = 1 To rngTblRcds.Rows.Count

        'Assume record is completely Null, and open record string for concatenation
        notNull = False
        rcdDetail = "('"

        'Evaluate field in the record
        For ch = 1 To rngColHeads.Count
            Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                    'if empty, append value of null to string
                Case Is = Empty
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                        Case Else
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                    End Select

                    'if not empty, set notNull to true, and append value to string
                Case Else
                    notNull = True
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                        Case Else
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                    End Select
            End Select
        Next ch

'If record consists of only Null values, do not insert it to table, otherwise
        'insert the record
        Select Case notNull
            Case Is = True
                rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
            Case Is = False
                'do not insert record
        End Select

Next cl

EndUpdate:
    'Check if error was encounted
    If Err.Number <> 0 Then
        'Error encountered.  Rollback transaction and inform user
        On Error Resume Next
        cnt.RollbackTrans
        MsgBox "Error! RentRoll Data upload was not succesful!", vbCritical, "Error!"
    Else
        On Error Resume Next
        cnt.CommitTrans
        
        MsgBox "RentRoll Data Upload was Succesful.", vbInformation, "Success!"
        
    End If

    'Close the ADO objects
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    On Error GoTo 0

End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I just realized it is VBA ran before this that causes the issue. I will post the true problem in a second.
 
Upvote 0
Below is the code presenting problems. It pulls data from another worksheet and pastes it in a sheet to be ready to be uploaded to a database. It is after this step that the find and replace no long works (works before running, but not after).

Code:
Sub PullQCRetailDT()

Dim maxvalue As Integer
Dim maxvalue2 As Integer

Application.ScreenUpdating = False


'Empty Contents of Upload file

UploadFile = ActiveWorkbook.Name
Sheets("Upload").Range("A8:AZ20").ClearContents
Sheets("Upload").Range("A31").ClearContents
Sheets("Upload").Range("A42:AZ44").ClearContents
Sheets("Upload").Range("A53").ClearContents
Sheets("RentRollUpload").Range("A5:H1500").ClearContents

'Activate target file and select data to copy
Workbooks.Open Filename:= _
Sheets("Upload").Range("E2"), ReadOnly:=True
        
'Select data from CF Summary tab
Sheets("Cashflow Summary").Activate
vFinancialData = Range("A5:AV17")
vMasterLoanData = Range("A33:J33")
vAddLoanData = Range("A43:M45")
vPropertyData = Range("A53:M53")

'Select data from RR tab
Sheets("RentRoll").Activate
maxvalue = ActiveSheet.Range("AB1").Value
maxvalue2 = ActiveSheet.Range("AC1").Value

vRRDate = Range("C4")
vTenants = ActiveSheet.Range("A9:H" & maxvalue)

'Activate upload file and place data to be uploaded
Workbooks(UploadFile).Activate
Sheets("Upload").Activate
Range("A8:AV20") = vFinancialData
Range("A31:J31") = vMasterLoanData
Range("A42:M42") = vAddLoanData
Range("A53:M53") = vPropertyData
Range("N53") = vRRDate


' Replace ' by blanks
Range("A8:AV20").Select
    Selection.Replace What:="'", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    
Range("A31:J31").Select
    Selection.Replace What:="'", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Range("A42:M42").Select
    Selection.Replace What:="'", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
                
                
Range("A53:M53").Select
    Selection.Replace What:="'", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
                
Sheets("RentRollUpload").Activate

ActiveSheet.Range("A5:H" & maxvalue - 4) = vTenants

Range("B5:B" & maxvalue).Select
    Selection.Replace What:="'", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        

'Delete excess blank rows in RentRollUpload
'ActiveSheet.Rows(maxvalue2 & ":1500").Select
'Selection.EntireRow.Delete


Application.ScreenUpdating = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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