Excel VBA apostrophe in cell value without using loop??

[dave816]

Sorry for the Excel question in an Access forum.......... .........I don't see an Excel forum and there's probably a reason for that but figured I'd give this a shot anyway. Again sorry, delete if necessary.

I'm adding an apostrophe into the cell value using a loop, but with 40k rows of data it takes 5 minutes.

'    For iCol = 1 To 8
'        For lCtr = 2 To lLastRow
'            Cells(lCtr, iCol).Select
'            sValue = Selection.Value
'            If Left(sValue, 1) <> "'" Then
'                sValue = "'" & sValue
'                Selection.Value = sValue
'            End If
'        Next
'    Next

If my cell value is ABC, the new value becomes 'ABC and cell value length = 3.

I tried using a cell formula which takes 1 minute.

' ActiveCell.Form ulaR1C1 = "=""'""&A1"

If my cell value is ABC, the new value becomes 'ABC and cell value length = 4.

I need the cell value length to = 3 but would prefer to use the formula instead of the loop to save time.

Any suggestions?

[ADezii]

Sorry for the Excel question in an Access forum.......... .........I don't see an Excel forum and there's probably a reason for that but figured I'd give this a shot anyway. Again sorry, delete if necessary.

I'm adding an apostrophe into the cell value using a loop, but with 40k rows of data it takes 5 minutes.

' For iCol = 1 To 8
' For lCtr = 2 To lLastRow
' Cells(lCtr, iCol).Select
' sValue = Selection.Value
' If Left(sValue, 1) <> "'" Then
' sValue = "'" & sValue
' Selection.Value = sValue
' End If
' Next
' Next

If my cell value is ABC, the new value becomes 'ABC and cell value length = 3.

I tried using a cell formula which takes 1 minute.

' ActiveCell.Form ulaR1C1 = "=""'""&A1"

If my cell value is ABC, the new value becomes 'ABC and cell value length = 4.

I need the cell value length to = 3 but would prefer to use the formula instead of the loop to save time.

Any suggestions?

I tried the following Looping Structure evaluating 8 Columns, 40,000 Rows (320,00 Cell Values), and it took 40 seconds in several trials.

Dim rng As Excel.Range
Dim rngCtr As Excel.Range

Set rng = Range("A1:H40000")

For Each rngCtr In rng
  With rngCtr
    If Left(.Value, 1) <> "'" Then
      .Value = "'" & .Value
    End If
  End With
Next

[dave816]

I have more code that does more loops through more columns. Its interspersed with numeric data columns where I loop through the 40k rows and convert to integers, then go back to more text columns to add the apostrophe. All in all I'm looping through 20 columns, 40k rows, give or take. Plus this sub moves so other data around, so the entire process takes 4.5 minutes. I ran it using the apostrophe formula instead of the loop and it took 1.5 minutes, but the data doesn't work (4 char).

Thanks for looking, appreciate it.

[NeoPa]

There are a few techniques that can be used in Excel that reduce the normal extreme slowness of executing code.

I have a routine that I often use to ensure more speedy running. You're welcome to use it. It switches calculation to manual mode (It doesn't automatically recalculate every cell every time any cell is changed) and it turns Screen updating off. Beware of this one. If it's not turned back on again before returning control to the operator the application appears to have hung. This is only appearance. It is simply not updatiung the screen.

'SwitchMode switches between 'View' and 'Process' modes.
Private Sub SwitchMode(strType As String)
    Static intCalcMode As Integer
    Dim shtThis As Worksheet

    With Application
        Select Case strType
        Case "Process"
            .StatusBar = "Processing"
            .ScreenUpdating = False
            If intCalcMode = 0 Then intCalcMode = .Calculation
            .Calculation = xlCalculationManual
        Case "View"
            .Calculation = IIf(intCalcMode = 0, _
                               xlCalculationAutomatic, _
                               intCalcMode)
            .ScreenUpdating = True
            .StatusBar = False
        End Select
    End With
End Sub

[Ashley Moorman]

Love the code! Added it to my personal Macro Book. Thanks :)

[NeoPa]

Pleased to hear it Ashley :-)