473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Excel VBA apostrophe in cell value without using loop??

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
5 5609
ADezii
8,834 Expert 8TB
@dave816
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
  3.  
  4. Set rng = Range("A1:H40000")
  5.  
  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
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
NeoPa
32,556 Expert Mod 16PB
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
  5.  
  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
Love the code! Added it to my personal Macro Book. Thanks :)
Oct 12 '10 #5
NeoPa
32,556 Expert Mod 16PB
Pleased to hear it Ashley :-)
Oct 12 '10 #6

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

Similar topics

13
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet...
3
by: Jeremy | last post by:
Hi, I have a webpage that is taking input from a form and using it as criteria to select data out of an sql database. The page displays an html table with the results. The user can then click a...
6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
3
by: acuttitta | last post by:
I ran into something really bizzaro today. A team member had me look into some export code for some data he was outputting. The data looks at shipping containers and attempts to look at how...
8
by: John Brock | last post by:
I am creating an Excel workbook using VB.NET, and have run into a problem. Excel at times insists on reformatting data that I enter into cells, e.g., converting "01234" to "1234", and this screws...
4
by: Hardy Wang | last post by:
Hi all, I have following code to read Excel content into a DataSet string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel...
3
by: pitchblack408 | last post by:
Hello I am using excel as my database and when I do an insert there is an apostrophe that appears in the cell where a string was inserted. For example '(474)343-3433 It appears that the...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.