Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: code (2002)

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code (2002)

    Please can anyone see (in the attached module) why I am getting all cells red instead of those that differ to col C??
    Attached Files Attached Files

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

    Re: code (2002)

    Your code doesn't do anything at all since r is not defined.

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

    Re: code (2002)

    Column C contains text values, column Q contains numbers. Text does not equal numbers.

    Does this do what you want?

    Sub A08_GetCertegyDataLoop()
    Dim r As Long
    For r = 2 To Range("C" & Rows.Count).End(xlUp).Row
    If Range("Q" & r) <> CStr(Range("C" & r)) Then
    Range("Q" & r).Interior.ColorIndex = 3
    Else
    Range("Q" & r).Interior.ColorIndex = xlColorIndexNone
    End If
    Next r
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    Thanks, perfect, sorry I ommitted too much of the code from the uploaded file.
    Another question.
    I have a messagebox with a very long message, and when it ie executed, it is dropping some of the message. Is there a way around this. Currently - Dim MSG as string.

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

    Re: code (2002)

    MsgBox shows a maximum of 1,024 characters. If you really need more, create a userform with a label and set the label's caption to the message string in the UserForm_Initialize event.

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    LOL. I'm only missing 3 words, that sounds like more hassle than it's worth! I'll ur.... shorten my message. Thanks

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    I'm getting Run-time error "6" - Overflow

    y3 = y2.Cells(Rows.Count, 7).End(xlUp).Row 'Last 0808 data row number

    Any idea's?
    Attached Files Attached Files

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

    Re: code (2002)

    Change the declaration

    Dim y3 As Integer

    to

    Dim y3 As Long

    An Integer has a maximum of 32,767 while an Excel 2002 sheet has 65,536 rows. A Long can go up to 2,147,483,647.

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    So that's why it was working yesterday on a smaller sample of data, but not working today in production.

    Brilliant, Thanks.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    One other confusion:
    The sheet that is being looked up is sorted by I ascending, D ascending and then C descending. is a unique reference number so if there is more than one identical item, then they should be newest down to lowest. The VLOOKUP appears to be bringing back the oldest item, not the newest? Either I need to sort differently, or.... Am I looping backwards??

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

    Re: code (2002)

    Your VLOOKUP is looking in W:X, that has nothing to do with the values in columns I, D and C.

  12. #12
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    VLOOKUP searches from the top down. I couldn't find a Vlookup in your file so I don't know which columns you are using in the formula. If you using column C as your "return" column, since it sorted descending, it will return data that is the oldest - assuming column C is a date/time.

    If you want to use a column the is sorted descending you can use a combination of Index and Match

    as an example:
    <table border 1><td>letter</td><td>Value</td><td>a</td><td>4</td><td>a</td><td>4</td><td>a</td><td>3</td><td>a</td><td>3</td><td>a</td><td>2</td><td>a</td><td>2</td><td>a</td><td>1</td><td>a</td><td>1</td><td>b</td><td>4</td><td>b</td><td>4</td><td>b</td><td>3</td><td>b</td><td>3</td><td>b</td><td>2</td><td>b</td><td>2</td><td>b</td><td>1</td><td>b</td><td>1</td><td>c</td><td>4</td><td>c</td><td>4</td><td>c</td><td>3</td><td>c</td><td>3</td><td>c</td><td>2</td><td>c</td><td>2</td><td>c</td><td>1</td><td>c</td><td>1</td></table>
    =VLOOKUP("b",$A$2:$B$25,2,0) returns a value of 4
    while
    =INDEX($A$2:$B$25,MATCH("b",$A$2:$A$25,1),2) returns a value of 1

    I've attached a sample workbook.


    addendum - I didn't see the text file of the macro - which is why I did not know what columns you were using.
    Attached Files Attached Files

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    I was a little rushed earlier, and did not paint the complete picture.

    The file being looked up is sorted by I ascending, D ascending, and C descending. There may be multiple instances of the contents in I and D, C is a unique item reference, so these end up in groups of identical items, sorted newest on top. I and D are then concatenated into X and the required column that we want to bring back in the lookup is cut and paste from J to the right of X (Y). BUT as J is cut, X:Y become W:X, as you pointed out. So W contains groups of identical items, newest on top. (Or so I think).

    However, the results being returned seem to be the oldest, not the newest, even though they are sorted descending <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I presume the lookup runs top to bottom and is not running upwards. I may need to test again tomorrow in production to check that the data is as I would expect. Can I pause the code to check?

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code (2002)

    Mike,
    As in your example, I would expect 4, but get 1.

    DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers. I wonder if this could be the problem....

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

    Re: code (2002)

    You can set a breakpoint by clicking in a line and pressing F9. Pressing F9 again will remove the breakpoint.
    Code execution will pause at a breakpoint. You can inspect the value of variables by hovering the mouse pointer over them.
    To execute code one step at a time, press F8.
    To resume automatic execution, press F5.

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
  •