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]

Hi Wigi

I was just going through the exchange we had a week or so ago and found that you had made two replies that I didn't acknowledge. Apologies. For some reason there wasn't a notification of your replies so I missed them.

In the exchange with MikeG you asked if your code stopped at 64,632 which seemed to occur when using the Transpose function that MickG used. The answer is that your code didn't stop at 64,632 but went the distance to just under the required 200,000 rows.

Also thank you for responding to my attempt at annotating your code. I have a couple of further queries. Part of your code is:

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

Question:  Why does VBA take the 'sq' to mean an array and not a range named 'sq'?
(by the way while I realise that an array can be called anything why do you use 'sq' and not something like 'Arr' or 'Ray'?)

Thanks for telling me how to use Code Tags however when I put [Code] and then preview the post the Code Tags don't seem to have worked.  Also when I try to colour the text for a comment I get the colour number but not the colour.  How do I get the Code Tags to work and change the font colour?

Thanks again for all your help.

With kind regards

David

 

[wigi]

Hello there

I thought there had to be something bizarre going on because you neglected my posts No problem.

sq is just the choice of the programmer. You can use other names too, as long as it's clear and remains clear (be consistent).

In fact, sq is a Variant since it is not declared explicitly. To work with ranges, you would use: Set sq = ... Note the Set word in front for the object variable of a range.

 

[DavidSCowan]

Hi Wigi

Thank you for your speedy reply - that's very clear.

Do you have any other thoughts about Code Tags? I tried your

 in front of the code but I can't get it to work.

Rgds

David

 

[wigi]

Hi

Yes sure:

Type

, then paste the code itself, then type [ /code] (without the space or this post will be code formatted too).

Depending on your option settings for the message board, you can also have an icon, #, which will put the code tags automatically. You can then just paste the VBA code in the middle of the 2 tags.

 

[DavidSCowan]

Hi Wigi

Thanks but "Type

, then paste the code itself, then type [ /code]"
is what I did but it doesn't seem to work for me.  It just comes out with the words [Code] in the text.  

When I first contacted you today I quoted some of your code and tried to put in the code tags but it came out as follows:

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

What am I doing wrong? 

David

 

[wigi]

It's a forward slash (/), not backward slash (\)