Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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]
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Did you delete a reply that you had posted?

  8. #8

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How about

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

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use wildcards in the condition for COUNTIF and SUMIF:

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

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 LastLast

Posting Permissions

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