Results 1 to 15 of 16
Thread: code (2002)

20081207, 21:36 #1
 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??

20081207, 22:12 #2
 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.

20081207, 22:15 #3
 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

20081208, 01:18 #4
 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.

20081208, 09:04 #5
 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.

20081208, 09:12 #6
 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

20081208, 20:59 #7
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: code (2002)
I'm getting Runtime error "6"  Overflow
y3 = y2.Cells(Rows.Count, 7).End(xlUp).Row 'Last 0808 data row number
Any idea's?

20081208, 21:12 #8
 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.

20081209, 01:15 #9
 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.

20081209, 01:45 #10
 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??

20081209, 02:07 #11
 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.

20081209, 02:19 #12
 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.

20081209, 07:13 #13
 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?

20081209, 07:19 #14
 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....

20081209, 08:30 #15
 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.