By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Excel VBA apostrophe in cell value without using loop??

P: 2
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.
Expand|Select|Wrap|Line Numbers
  1. '    For iCol = 1 To 8
  2. '        For lCtr = 2 To lLastRow
  3. '            Cells(lCtr, iCol).Select
  4. '            sValue = Selection.Value
  5. '            If Left(sValue, 1) <> "'" Then
  6. '                sValue = "'" & sValue
  7. '                Selection.Value = sValue
  8. '            End If
  9. '        Next
  10. '    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.FormulaR1C1 = "=""'""&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?
Apr 28 '10 #1
Share this Question
Share on Google+
5 Replies

Expert 5K+
P: 8,638
I tried the following Looping Structure evaluating 8 Columns, 40,000 Rows (320,00 Cell Values), and it took 40 seconds in several trials.
Expand|Select|Wrap|Line Numbers
  1. Dim rng As Excel.Range
  2. Dim rngCtr As Excel.Range
  4. Set rng = Range("A1:H40000")
  6. For Each rngCtr In rng
  7.   With rngCtr
  8.     If Left(.Value, 1) <> "'" Then
  9.       .Value = "'" & .Value
  10.     End If
  11.   End With
  12. Next
Apr 29 '10 #2

P: 2
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.
Apr 29 '10 #3

Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. 'SwitchMode switches between 'View' and 'Process' modes.
  2. Private Sub SwitchMode(strType As String)
  3.     Static intCalcMode As Integer
  4.     Dim shtThis As Worksheet
  6.     With Application
  7.         Select Case strType
  8.         Case "Process"
  9.             .StatusBar = "Processing"
  10.             .ScreenUpdating = False
  11.             If intCalcMode = 0 Then intCalcMode = .Calculation
  12.             .Calculation = xlCalculationManual
  13.         Case "View"
  14.             .Calculation = IIf(intCalcMode = 0, _
  15.                                xlCalculationAutomatic, _
  16.                                intCalcMode)
  17.             .ScreenUpdating = True
  18.             .StatusBar = False
  19.         End Select
  20.     End With
  21. End Sub
Apr 30 '10 #4

P: n/a
Love the code! Added it to my personal Macro Book. Thanks :)
Oct 12 '10 #5

Expert Mod 15k+
P: 31,494
Pleased to hear it Ashley :-)
Oct 12 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.