Faster code for extracting data using Instr

[DavidSCowan]

Hi There

I am using InstrRev to extract items from around 200,000 cells in Col A. The problem is that the macro takes around 3 hours to run. I am relatively inexperienced in VBA so I was wondering if there is a way to make the code run faster.

Example cells in Col A are:

ADMIRATION SYBN OIL MRGR STICK 16 OZ
LUDWIG DAIRY BTR UNSL SOLID 7.14 OZ
NU-MAID VGTB OIL ASSRTD COMMON CO SLTD TUB 1 CT
CANOLA HARVEST CNL OIL CTNS OIL SPRD TUB 33 PCT FWR CLR 16 OZ
AMUL BTR SLTD SOLID 17.64 OZ

The purpose of the macro is to write the sizes or weights in the Col A cells to Col B and the measure (e.g. OZ for ounces) to Col C. So the output from the cells above would be:

Col B
16
7.14
1
16
17.64

Col C
OZ
OZ
CT
OZ
OZ

The code is:

Sub Instr_Simple_2()
finalrow = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For i = 2 To finalrow
If InStrRev(Cells(i, 1).Value, " OZ") <> 0 Then

position1 = InStrRev(Cells(i, 1), "OZ")
position2 = InStrRev(Cells(i, 1), " ", position1 - 2)
Gap = position1 - position2 - 2
Cells(i, 2) = Mid(Cells(i, 1), position2 + 1, Gap)
Cells(i, 3).Value = "OZ"

ElseIf InStrRev(Cells(i, 1).Value, " CT") <> 0 Then
position1 = InStrRev(Cells(i, 1), "CT")
position2 = InStrRev(Cells(i, 1), " ", position1 - 2)
Gap = position1 - position2 - 2
Cells(i, 2) = Mid(Cells(i, 1), position2 + 1, Gap)
Cells(i, 3).Value = "CT"
Else
End If

Next i
Application.ScreenUpdating = True

End Sub

How can I make the code run faster?

Can someone help please. Thank you

With kind regards

David

 

[DavidSCowan]

That's great - thank you

 

[DavidSCowan]

Mick

With the help of your annotations I now understand how your code works so thank you for them. However I have some minor queries:

When you declared Ray and nRay why weren't there any ()? Also other objects that are declared are declared as a type of object (Dim Rng as Range) but this wasn't done here and the IntelliSense doesn't offer Array as an option so presumably that is not how arrays are declared. Do you know why this is, could you explain please? And nnRay wasn't declared as a dynamic array at the declaration stage was there a reason for this? I thought that if variables weren't declared they are assumed to be variants so if the declaration doesn't declare the type of object why declare at all?

Also Wigi if you could annotated your code that would be really useful - I understand some of it but not all.

With kind regards

David

 

[wigi]

Also Wigi if you could annotated your code that would be really useful - I understand some of it but not all.

Sure, make annotations to my code the way you see and understand it. I will correct and extend after that you post it here.

 

[MickG]

I think, ultimately you will be better informed googling these problems i.e:-

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 id=td_post_2925373 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>http://www.beyondtechnology.com/vba010.shtml</TD></TR></TBODY></TABLE>
but for what is worth, here's my take.
I always declare my variables, in the long run its a time and headache saver, when the problems arise
If the array type is not explicitly shown it will be of a "Variant " Type and is possibly also a sign of lazyness on my part.
Arrayn "nRay being the Variant array and "Ray" being from the "Split" function.
When you add "()" after you dimension the array, you in effect "Dim" the array without a dimension, so you can to resize the array at a later stage ("Dynamic")
Ray, nnRay and nRay are not Dynamic arrays, in the sense that there size is detemined when values are assigned to them.
In the case of nnRay, because the final number of rows will be the same as the "Rng" row size it is dimensioned the same, But if the"nnRay" where smaller than "Rng" count , you could still use it in the final line by assigning the final results to less rows.
Regards Mick

 

[DavidSCowan]

Hi Wigi

Thank you very much for the offer.

Sub Instr_Simple_2_Wigi_Annotated()

StartTime = Timer()

'Defining a range of Col A. I don't understand why offset. Also why (, 2), is this the same as (0,2)?
'Is the range now 2 cols wide or just moved 2 columns to the right?
sq = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 2))

'looping through each cell. UBound(sq,1) which is the last cell of the range
For i = 1 To UBound(sq, 1)

'Presumably the If statement is necessary just in case there isn't a " CT" or a " OZ" a) As it happens there always is one of the other and the code works without it and b) when we assume that the last word in the cells are the type of measure and the second to last word is the numbers of units the whole thing is independent of " OZ" and " CT"
If InStrRev(sq(i, 1), " CT") Or InStrRev(sq(i, 1), " OZ") Then

'This creates an array out of the words in each cell but I thought the Split function needed a delimiter. So I am afraid I don't understand Split(sq(i, 1))
st = Split(sq(i, 1))

'Writes the last but one item in the cell which are always the nos. of OZs or CTs to the "i"th row 2nd column of sq
sq(i, 2) = st(UBound(st) - 1)

'Writes the last word in the cell which are always the types of units (OZs or CTs) to the "i"th row 3rd column of sq
sq(i, 3) = st(UBound(st))
End If
Next

'Writes the result out back on the worksheet

Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 2)) = sq

FinishTime = Timer()
RunTime = FinishTime - StartTime
MsgBox "Running time for macro " & RunTime & "Seconds"

End Sub

I guess I sort of understand it but there are quite a few details that I don't get which means I will have difficulty writing my own code for slightly different problems.

On a more general presentational note I don't understand how to put message board code into a box as others seem to do. Or to put the comments in green. When I try to do this it doesn't work (it just gives the colour numbers!)

Thanks again for your help.

David

 

[wigi]

Sub Instr_Simple_2_Wigi_Annotated()

'track the current time
StartTime = Timer()

'why (, 2), is this the same as (0,2)? YES
'we retrieve the contents of range A2:Cx, where x is the last row containing data - with column A as the basis to determine that last row
'.Offset(, 2) instructs to take column C, ie. form column A two columns to the right
'sq will contain the values of the cell and will be worked on
'Is the range now 2 cols wide or just moved 2 columns to the right?
'The range is 3 columns wide: A, B, C
    sq = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 2))

    'looping through each cell. UBound(sq,1) which is the upperbound for rows. So yes, loop from the first to the last cell
    For i = 1 To UBound(sq, 1)

        'Presumably the If statement is necessary just in case there isn't a " CT" or a " OZ"
'YES

   a) As it happens there always is one of the other and the code works without it and
'then you could get rid of the IF statement

b) when we assume that the last word in the cells are the type of measure and the second to last word is the numbers of units the whole thing is independent of " OZ" and " CT"
'YES, but it's an assumption, it might not always be the case. Just be aware.

        If InStrRev(sq(i, 1), " CT") Or InStrRev(sq(i, 1), " OZ") Then

            'This creates an array out of the words in each cell but I thought the Split function needed a delimiter.  So I am afraid I don't understand Split(sq(i, 1))
'You are right. Without a delimiter, the space is the delimiter. By default, Split uses a space.
            st = Split(sq(i, 1))

            'Writes the last but one item in the cell which are always the nos. of OZs or CTs to the "i"th row 2nd column of sq
'YES
            sq(i, 2) = st(UBound(st) - 1)

            'Writes the last word in the cell which are always the types of units (OZs or CTs) to the "i"th row 3rd column of sq
'YES
            sq(i, 3) = st(UBound(st))
        End If
    Next

    'Writes the result out back on the worksheet
'YES
    Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 2)) = sq

'track how "long" the macro needed
FinishTime = Timer()
RunTime = FinishTime - StartTime
MsgBox "Running time for macro " & RunTime & "Seconds"

End Sub

 

[wigi]

To add code tags, you should type in your post:

then paste your code, and lastly, type:

['/code]

WITHOUT the ' in front of the /

Thanks for the consideration.

 

[DavidSCowan]

Hi Mick

Thank you for taking the trouble to reply and you make some good points.

Prior to writing back to you I tried to answer the question myself and did some searching on the Internet and looked in various books but they all talk about declaration statements like: Dim myArray(10,100) or myArray(1 to 10,1 to 100) etc.

I take your point about Dim myArray() means it is dynamic and in the code you wrote there is no need for dynamic array. But what I don't understand is when there is the statement Dim nRay and nothing else - how does VBA know it is an array at all? There are static arrays - myArray(1 to 10,1 to 100), and dynamic arrays - myArray() but what is Dim nRay? Is there some other sort of array?

Later in your code there is the line nRay = rng. Presumably this means the array nRay has been populated with the contents of rng. But how did vba know nRay was an array? Is it that deep down variants are arrays by another name?

Sorry maybe I am being too pedantic I will quite understand if you are disinclined to continue this conversation!

Thanks again for your help.

David

 

[MickG]

A "Variant" variable can hold any variable type.
If you imagine a "Variant" variable like a "Bus" it does'nt matter if there's Just a Driver or the "Bus" is full up with people, its still a bus.
At the start of the code we don't know how big to make the array (nnRay), so we wait until we find the size of the range of cells, as returned from the "Set" command, then we can "redim" the Array size to the size of the "Rng" Range (Rng.count).
In the case of the Variant array when we assign the data to the variable (Initialize), the size of the Variant array is determined by the size of the range we assign to it.
So when you initialise a "Variant" variable, Whats you put in it, detemines how it acts.
I think its quite difficult to understand from all this waffle.
You need to have a go writing lots of code. Make sure you use "Option Explicit" to find all your errors. There's no substitute for practice.
When I see useful bits of coder I store them away, for future reference.
Sorry if I sound patronising.!!!
Regards Mick
P.S. See if you can get this book:- "Excel VBA in easy steps" by "Ed Robinson"
(Make sure its by him)

 

[DavidSCowan]

Hi Mick

Thank you for your reply, I understand what you are saying and no not patronising. Yes I need to practice by writing lots of code - the exchange with you (and Wigi) has been very helpful so thank you again for taking the trouble.

With kind regards

David