# Thread: Looping (again) :-(

1. Hi,

The code snippet attached is part of a much bigger, recently constructed project. It does what I want but is very sluggish!!

I have commented out the code. I need to be sure that I am only evaluating where necessary, there are 4 If loops, once the data is found, the next IF's should be ignored. My question is: Is my approach right? Or am I going about this completely the wrong way?

Thanks for any advice.

[attachment=82792:11.03.09.txt]

2. With these numbers, you should use a database instead of Excel.

The use of RngFind.Offset(0, -8) makes no sense. If the Find action failed, RngFind will be Nothing, and hence RngFind.Offset(0, -8) will cause an error. If the Find action succeeded, RngFind will not be Nothing and hence RngFind.Offset(0, - 8) will not be nothing either.

3. With these numbers, you should use a database instead of Excel.
Not an option unfortunately, at least for the forseeable future.

My apologies in advance for my stupidity, but I am confused.
The use of RngFind.Offset(0, -8) makes no sense. If the Find action failed, RngFind will be Nothing, and hence RngFind.Offset(0, -8) will cause an error. If the Find action succeeded, RngFind will not be Nothing and hence RngFind.Offset(0, - 8) will not be nothing either.
When you say "cause an error", what do you mean? This does execute (on a smaller scale), using the first and third IF's, but I have not tested with the second and fourth. But I use the .Offset(0, -8) on the third?

If RngFind.Offset(0, 1) > 0, Get data from y4.
If the data is found and returned by looking at y4, end.
If not, look in y5.

If RngFind.Offset(0, -8) has data in it (found above), end.
If not:
RngFind, Get data from y4.
If the data is found and returned by looking at y4, end.
If not, look in y5.

4. If Access or another database is not an option, you shouldn't try to process hundreds of thousands of records.
If you do need to process hundreds of thousands of records, a database is essential. Saying that it isn't an option means that your company's priorities are completely wrong.

Oh wait, RngFind is not the result of Find, so it is never Nothing and hence RngFind.Offset(0, -8) is never Nothing either. If you want to check whether the cell is filled, use

RngFind.Offset(0, -8) = ""

One more thing: And has precedence over Or, so

If RngFind.Offset(0, 1) > 0 And RngFind.Offset(0, -8) Is Nothing Or RngFind.Offset(0, -8) = "" Then

means

If (RngFind.Offset(0, 1) > 0 And RngFind.Offset(0, -8) Is Nothing) Or (RngFind.Offset(0, -8) = "") Then

It's up to you to decide whether that is what you intended.

5. Morning Hans,

It's been a long old night, but I've come on leaps and bounds

[codebox]
If RngFind.Offset(0, 1) > 0 Then
Set RngFound = y4.Offset(0, -18).Find(What:=RngFind.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole)
If Not RngFound Is Nothing Then
RngFind.Offset(0, -12) = RngFound.Offset(0, 5)
RngFind.Offset(0, -11) = RngFound.Offset(0, 6)
RngFind.Offset(0, -10) = RngFound.Offset(0, 7)
RngFind.Offset(0, -9) = RngFound.Offset(0, 8)
RngFind.Offset(0, -8) = RngFound.Offset(0, 9)
RngFind.Offset(0, -7) = RngFound.Offset(0, 10)
RngFind.Offset(0, 1) = ""
End If
End If

If RngFind.Offset(0, 1) > 0 Then
Set RngFound = y5.Offset(0, -18).Find(What:=RngFind.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole)
If Not RngFound Is Nothing Then
.....................
End If
End If

If RngFind.Offset(0, -8) = "" Then
Set RngFound = y4.Find(What:=RngFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not RngFound Is Nothing Then
...................
End If
End If

If RngFind.Offset(0, -8) = "" Then
Set RngFound = y5.Find(What:=RngFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not RngFound Is Nothing Then
..............
End If
End If
[/codebox]

Resetting RngFound to "" if the first IF is met, means the 2nd is not evaluated.

If RngFind.Offset(0, -8) = "" Then < is sufficient for the 3rd and 4th.

Moving the "Set RngFound" statements inside the IF > made a massive difference in speed!!

All that combined with referencing external files, instead of bringing in (and later deleting) 25 meg of data, solely for the purpose of extracting a few hundred datasets, means that the procedure has sped up a few thousand percent!! All in all, it's been a great learning experience!

Anyway, the hard stuff done and dusted, the little things I can't get right.......

RngFind.Offset(0, -7).Value = "00000000" < produces a single zero value, but I want an 8 digit string. How please?

6. [quote name='VegasNath' post='764991' date='12-Mar-2009 11:57']RngFind.Offset(0, -7).Value = "00000000" < produces a single zero value, but I want an 8 digit string. How please?[/quote]
Insert a single quote (apostrophe) before the value:

RngFind.Offset(0, -7).Value = "'00000000"

7. Did you delete a reply that you had posted?

8. Yes, sorry, it was a very stupid question!

9. maybe it was not that stupid after all as I still cannoy figure it out:
x1 = w1.Cells(w1.Rows.Count, 12).End(xlDown).Row < I am trying to find the last used cell number in column 12 from top down (there are blank rows before the next dataset, so I want to find the end of the first)

x1 = w1.Cells(1, 12).End(xlDown).Row

11. Code:
```For Each RngFind In y1
RngFind.Offset(0, -6).Value = Application.VLookup(RngFind.Value, y3, 2, False)
If IsError(RngFind.Offset(0, -6)) Then
RngFind.Offset(0, -5) = "Not in susp"
RngFind.Offset(0, -6).ClearContents
ElseIf RngFind.Offset(0, -6) = 0 Then
RngFind.Offset(0, -5) = "In susp Not Found"
RngFind.Offset(0, -6).ClearContents
Else
RngFind.Offset(0, -5) = "Found in susp"
End If
Next RngFind```
'ElseIf Left(RngFind.Offset(0, -6), 1) <> "4" Or Left(RngFind.Offset(0, -6), 1) <> "5" Then
'RngFind.Offset(0, -5) = "In susp Other"
'RngFind.Offset(0, -6).ClearContents

I need to add this condition into the above working code, but I can't get the logic right. Please can you see where I should add it. Thanks

12. You should insert these lines immediately above the line with Else.
You should use And instead of Or. The condition

Left(RngFind.Offset(0, -6), 1) <> "4" Or Left(RngFind.Offset(0, -6), 1) <> "5"

is ALWAYS true.

13. Thanks Hans,

=COUNTIF(Rejections!I:I,"65-06 HBBA 99 6506") ......... or I2:I5000

How can I combine thiis with Left? I need Left 5 = "65-06". I tried sumproduct also but can't get a result.

14. You can use wildcards in the condition for COUNTIF and SUMIF:

=COUNTIF(Rejections!I:I,"65-06*")

15. Thanks (again).
What is the syntax for copying a worksheet and renaming it in vba, without knowing what the auto generated name will be?

w1.Copy After:=Sheets("w1")............ ??

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•