Avoid looping =>Speed code manipulating text

[Gavona]

I want to manipulate the existing text in a column but my existing code takes much too long to run

Sub test()
Dim myRange As Range
Dim c As Range
Set myRange = Selection
For Each c In myRange
    Select Case Right(c.Value, 4)
    Case strCurrentYear                                       'Current Year data by period
        c.Value = Right(c.Value, 4) & "_" & Left(c.Value, 2)
    Case Is < strCurrentYear
        c.Value = "_Previous Years"
    Case Else 'future years - very few records will meet this condition
        c.Value = Str(Right(c.Value, 4))
    End Select
Next c
End Sub

I am thinking that if I can find a way to first convert all the cells to c.Value = Right(c.Value, 4) & "_" & Left(c.Value, 2) without looping through every record then I can deal with the other cases using filters.
So unless you have a better solution I want to achieve the equivalent of this:

For Each c In myRange
            c.Value = Right(c.Value, 4) & "_" & Left(c.Value, 2)
Next c

Thanks for looking,


Gavin

 

[SkipVought]

hi,

I have several questions.

Is this a DATE column or is the data really just TEXT?

Why are you doing this in VBA? Why not formulas on the sheet? THAT would calculate much faster!

Any further questions, would be a result of the answer to my first question.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Hi Skip,
Q Is this a DATE column or is the data really just TEXT?

A. The data comes as text examples are:
01 2009,12 2008,10 2012,11 2012
After conversion of these fields my VBA presents it in a pivot (must work across versions from xl2003).
The data in question is a column field. I thought about converting to date but (i)I would not know how to Group all previous years into a single column and (ii) Calendar years don't equate to Accounting years (01_2012 is in fact April 2012)

Q. Why are you doing this in VBA? Why not formulas on the sheet? THAT would calculate much faster!
A. VBA because it is part of a bigger process used by many. Download the data, run the macro and see information in a pivot.
Sometimes there are 100s of rows, sometimes thousands. I would not want to leave as formulae as the conversion is once only.
I could indeed insert a column, populate with a formulae to manipulate the string then copy to values. (Maybe I should text to columns to spilt the field initially and avoid Left and Right functions). I agree that would be quicker but do you think it would be best?
Is there a way of making something like
myRange.value=[Right(myRange.Value, 4)&..........] do the job even quicker (I could not get that to work by the way)

(I have screen updating and calculation set to False)



Gavin

 

[SkipVought]

If I understand you correctly, your data source, prior to getting into Excel are TEXT files.

If you were to IMPORT via Data > Get external Data > Text files... then you could PARSE each part of your data into a separate field, thereby eliminating this step altogether. So you data COULD look like
[tt]
mo1 yr1 mo2 yr2 mo3 yr3 mo4 yr4
01 2009 12 2008 10 2012 11 2012
[/tt]
that's one possibility.

The other is to again IMPORT, but parse only on the COMMA and then use the formula in the table. Any formula that you have previously added to your table, will automatically adjust and calculate when new data is IMPORTED via the REFRESH method on the sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MakeItSo]

That or you could still set a formula in a unused and usually hidden column, say column ZZ, the formula for ZZ1 being:

=IF(RIGHT(A1,4)="2013",CONCATENATE(RIGHT(A1,4),"_",LEFT(A1,2)),IF(VALUE(RIGHT(A1,4))<2013,"_Previous Years",RIGHT(A1,4)))

Then simply read that value in your VBA code.
No?



“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[DaveInIowa]

How many cells are you processing and what do you consider to be 'much too long'? I ran a test over 10,000 cells and it took 1 second. I'm wondering if comparing String values are slowing things down, maybe working with the Left and Right substrings as Integers would help. Also for your Select/Case structure, make sure you list the most common comparisons first.

Dim mm As Integer
Dim yyyy As Integer
Dim currentYear As Integer
currentYear = CInt(strCurrentYear)
For Each c In Selection
    If (c.Value Like "## ####") Then
        mm = CInt(Left(c.Value, 2))
        yyyy = CInt(Right(c.Value, 4))
        c.Value = IIf(yyyy < currentYear, "_Previous Years", IIf(yyyy = currentYear, yyyy & "_" & mm, yyyy))
    End If
Next

 

[Gavona]

Thanks guys, I will do some experimenting - maybe not tomorrow though.
The field in question is actually in a column
The data comes from SAP and opens directly in Excel. Doing it like this is simpler for the end user.
I need to time it for you but it is a case of go make a cup of coffee and have a chat at the coffee machine at the moment. I am a bit puzzled why Dave's solution is soo much faster.
I think 2,000 rows would be typical, 10,000 rows would be a large file but they have reached 50,000 in extreme cases. Beyond that the SAP transaction times out after running for 45 minutes.

MakeItSo: I don't think I want the formula against every row except as an interim step - too much impact on file size and calculation performance. But I could copy the formula down, calculate the range and then pastespecial to values. I will try this approach first.

You are of course right Skip. If I split the field using text to columns (as I am not importing) then that will reduce subsequent processing need. If I transform the Year column to include "Previous Years" etc then I don't need to then concatanate the year and month - I can handle the rest in the pivot show-hide detail. I will try this second to see how that affects things.

DaveinIowa: Very interesting how fast yours was. I'll do some testing see if I can match your sort of results. And experiment to see if strings or select case are particularly inefficient.

Gavin

 

[SkipVought]

Use Application.Screenupdatimg = False

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Also set calculation to MANUAL

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

(I have screen updating and calculation set to False)

I'll let you know how I get on on the next few days.

Gavin

 

[SkipVought]

sorry I missed that.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]