PDA

View Full Version : Check the order



priya_antony
08-22-2007, 03:26 AM
Hi,
Actually i want check the structure placed in column D of the excel sheet and needs to validate the same
i require the coding partof it in VB


A.02222----------------row 1
A.02222.1-------------row2
A.02222.1.01
A.02222.1.02
A.02222.2
A.02222.2.01
A.02222.3

Invalid structure

A.02222----------------row 1
A.02222.1-------------row2
A.02222.1.01
A.02222.1.02
A.02222.1.04-----------------invalid
A.02222.1.03
A.02222.2.02-----------------invalid
A.02222.2
A.02222.2.01
A.02222.3

Kindly help

lucas
08-22-2007, 05:48 AM
From the macro recorder. if you have more than 100 rows you will have to change that in the code.
Sub Macro1()
Rows("1:100").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

priya_antony
08-23-2007, 01:26 AM
Hi

Actually my excel sheet column D contains the data as follows
I need to check the validity of this kind of structure in VBA

A.02222
A.02222.1
A.02222.1.01
A.02222.1.02
A.02222.1.03
A.02222.2
A.02222.2.01 ?Values not required to be consecutive but should
A.02222.2.03 ?keep the order
A.02222.2.05
A.02222.2.07
A.02222.3
A.02222.3.1

Kindly help
Thanks and Regards
Priya

Bob Phillips
08-23-2007, 01:27 AM
It would be good to keep topics in a single thread rather than proliferate the information across many threads, and it needs a lot more explanation than that, it would need a mind-reader to know what you mean.

priya_antony
08-23-2007, 01:40 AM
Hi

Actually what I require is to check that whether the order is followed in column D i.e

A.02222 this value is in cell(d1)
A.02222.1 this value is in cell(d2) and follows
A.02222.1.01
A.02222.1.02
A.02222.1.03
A.02222.1.05 --------------this is since 3 should follow 4
A.02222.1.04

Thanks
Priya

Bob Phillips
08-23-2007, 02:05 AM
You could use conditional formatting with a formula of

=AND(D2<>"",D1>D2)

priya_antony
08-23-2007, 02:11 AM
Hi
Lucas

I tried the above code but it gives an error saying application or object defined error

Thanks
Priya

Bob Phillips
08-23-2007, 02:15 AM
Good idea, keep two threads on the same subject running at the same time.

You buddy are now on my 'kill' list.

priya_antony
08-23-2007, 02:25 AM
Hi
Actually i didnt remember i had posted earlier so after getting the reply for the other post i continued with this one
Sorry for the inconvienence

Thanks
Priya

lucas
08-23-2007, 06:25 AM
so where is the other post? How about a link to it at least

mdmackillop
08-23-2007, 09:51 AM
Threads merged.

Aussiebear
08-24-2007, 02:22 AM
Hmmm....... Bob the Assassain. Nope just doesn't have that ring to it.

big_bear
08-24-2007, 08:30 AM
Sub check()
Set d = Columns("D").Find("")
For i = 1 To d.Row - 1
If Cells(i, 4) > Cells(i + 1, 4) Then Cells(i, 5) = "AAAAAAAAAAaaaaaaaaaaarrrgrh!"
Next i
End Sub


It's not the worst thing ever, but work on your forum etiquette:
@ useful thread title
@ no cross-posting
@ not being a general arse. You get the help for free, obeying the rules is a pretty small price.