View Full Version : Help needed!!!!
GohLS
11-20-2008, 09:10 AM
Hi i am currently working on a excel formular,
and i am stuck, need some help from you guys.
I need to check if the "Order Number" is complete or Not Complete,
however checking by manual is very hard.
So i would like to check if you guys can help on this.
I have attached the excel for reference.
anyone can help by using excel formular or marco is appreciate.
I did some explaination in the excel attached.
Kindly help
Bob Phillips
11-20-2008, 09:32 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextItem As Long
Dim BreakIndex As Long
Dim aryItems As Variant
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
ReDim aryItems(1 To LastRow)
For i = 1 To LastRow
If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Not Complete" Then
If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then
NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i
BreakIndex = NextItem
For i = 1 To LastRow
If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Complete" Then
If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then
NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i
For i = 1 To NextItem
.Cells(i + 3, "F").Value = aryItems(i)
.Cells(i + 3, "G").Value = IIf(i > BreakIndex, "", "Not ") & "Complete"
Next i
End With
End Sub
Bob Phillips
11-20-2008, 09:32 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextItem As Long
Dim BreakIndex As Long
Dim aryItems As Variant
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
ReDim aryItems(1 To LastRow)
For i = 1 To LastRow
If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Not Complete" Then
If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then
NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i
BreakIndex = NextItem
For i = 1 To LastRow
If .Cells(i, TEST_COLUMN).Offset(0, 1).Value = "Complete" Then
If IsError(Application.Match(.Cells(i, TEST_COLUMN).Value, aryItems, 0)) Then
NextItem = NextItem + 1
aryItems(NextItem) = .Cells(i, TEST_COLUMN).Value
End If
End If
Next i
For i = 1 To NextItem
.Cells(i + 3, "F").Value = aryItems(i)
.Cells(i + 3, "G").Value = IIf(i > BreakIndex, "", "Not ") & "Complete"
Next i
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.