|
Subject:
|
Need help with mutilevel checks
|
|
Posted By:
|
codeFox
|
Post Date:
|
4/15/2008 1:46:37 AM
|
Hi there,
Appreciate if someone could advise the conditional check structure where I have to check different columns against each row as follows:
There are basically two main conditions, Order date and Payment term and tenor. All orders on or before 31-Mar will have appropriate service rate charge. For example, first the code should check the order date of each row, then the payment term and it's tenor, then it should move on to next columns to see what service rate is charged in the given data.
What I want to know what kind of multi level conditional check code should I go for, "select case" or "If else if structure"? Don’t expect you to write full code for me just a hint as to what would be recommended approach. This will be sort of automation to the manual AutoFilter approach where I first put filter on Order date, then put filter on all LC 90 days order to see what rates are given. I guess you get an idea what I mean. Remember, I have daily 15000 or more rows to check, which for the time being is done through normal AutoFilter approach which is tedious.
Cust PO No Item No Price Sec Price Order Date PayTrm Tenor RATE%
ABC 1234 ITEM1 5.3940 5.8000 19-Mar-2008 L/C 90 DAYS 1.50
ABC 1233 ITEM2 5.6730 6.1000 19-Mar-2008 L/C 60 DAYS 2.50
XYZ 1287 ITEM1 5.3940 5.8000 19-Mar-2008 L/C 90 DAYS 1.50
XYZ 1287 ITEM2 5.6730 6.1000 19-Mar-2008 L/C 90 DAYS 1.50
XYZ 1287 ITEM3 5.5800 6.0000 19-Mar-2008 L/C 90 DAYS 1.50
XYZ 1287 ITEM4 5.5800 6.0000 19-Mar-2008 L/C 90 DAYS 1.50
EFG 2566 ITEM1 6.8820 7.4000 03-Apr-2008 L/C 90 DAYS 2.50
EFG 2566 ITEM2 4.0455 4.3500 03-Apr-2008 L/C 90 DAYS 2.50
EFG 2566 ITEM3 4.0455 4.3500 03-Apr-2008 L/C 90 DAYS 2.50
|
|
Reply By:
|
Shasur
|
Reply Date:
|
4/15/2008 8:49:27 AM
|
Hi
A better solution would be to use Excel as Database. If you are familiar with ADO, you can use this. With this you can query easily and get the data
http://vbadud.blogspot.com/2006/09/ado-connection-string-for-excel.html
Here is a sample code
Sub Excel ADO()
Dim cN As ADODB.Connection '* Connection String
Dim RS As ADODB.Recordset '* Record Set
Dim sQuery As String '* Query String
Dim i1 As Long
Dim lMaxRow As Long '* Last Row in the Sheet
Dim iRevCol As Integer '*
Dim i3 As Integer
On Error GoTo ADO_ERROR
Set cN = New ADODB.Connection
cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
cN.ConnectionTimeout = 40
cN.Open
Set RS = New ADODB.Recordset
lMaxRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iRevCol = 2
For i1 = 2 To lMaxRow
Application.StatusBar = i1
sAssy = Trim$(Cells(i1, 1).Value)
sQuery = "Select * From [Sheet1$]"
RS.ActiveConnection = cN
RS.Source = sQuery
RS.Open
If RS.EOF = True And RS.BOF = True Then
Cells(i1, iRevCol).Value = "N/A"
GoTo TakeNextRecord
End If
RS.MoveFirst
i3 = 1
Do Until RS.EOF = True
i3 = i3 + 1
Loop
sRev = Trim$(RS("Rev").Value)
If LenB(sRev) <> 0 Then
Cells(i1, iRevCol).Value = sRev
Else
Cells(i1, iRevCol).Value = "NULL"
End If
TakeNextRecord:
If RS.State <> adStateClosed Then
RS.Close
End If
Next i1
If Not RS Is Nothing Then Set RS = Nothing
If Not cN Is Nothing Then Set cN = Nothing
ADO_ERROR:
If Err <> 0 Then
Debug.Assert Err = 0
MsgBox Err.Description
Resume Next
End If
End Sub
Cheers
Shasur
http://www.dotnetdud.blogspot.com
VBA Tips & Tricks (http://www.vbadud.blogspot.com)
|
|
Reply By:
|
codeFox
|
Reply Date:
|
4/16/2008 12:25:44 AM
|
Dear Shasur
Thanks for your comments. I really appreciate the time and effort you must have taken to post it. It will indeed be of great help sometime in near future.
However, let me elaborate further that in this every case, the objective is not to pull a recordset but to check each and every row of EXCEL data for more than one conditions. For example first on the basis of order date, then the payment type, finally the tenor (30 , 90 days etc), once these 3 criteria are identified, the CODE will be checking the corresponding value in the rate column on the same row to see if the rate is appropriate and in case of any discrepancy I will use the ActiveCell.Address.Offset(0,1) method or something like this to add comments in rightmost + 1 column about the discrepancy,
for example, "The correct rate of commission should be 3%"
What I am trying to figure out how should I structure my code to check various columns on the same ROW for various conditions.
I hope this would have clarified. Thanks again anyway if I am dumb enough to properly communicate my requirement/problem.
Thanks in advance.
|
|
Reply By:
|
Shasur
|
Reply Date:
|
4/16/2008 2:31:11 AM
|
Try if the following code helps you :
Sub Loop_Thru()
Dim i As Long
Dim iMax As Long
Dim OrderDt As Date
Dim PaymentType As String
Dim Tenor As Integer
iMax = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 2 To iMax
' Reset Values
OrderDt = vbNull
PaymentType = ""
Tenor = 0
' Set Values
OrderDt = ActiveSheet.Cells(i, 6).Value
PaymentType = ActiveSheet.Cells(i, 7).Value
Tenor = ActiveSheet.Cells(i, 8).Value
Select Case OrderDt
Case DateDiff("d", OrderDt, Now()) < 10
Case DateDiff("d", OrderDt, Now()) < 20
Case DateDiff("d", OrderDt, Now()) < 30
Select Case PaymentType
Case "L/C"
' Code for Tenor Checks
Case "Else"
ActiveSheet.Cells(i, 6).Value = "Error in Payment Type"
End Select
End Select
Next i
End Sub
Here you can use both Select and If Else interchangeably. If the processing takes quite some time, then you need to re-organise the checks, for example, checking the most occurring case first etc.
Cheers
Shasur
http://www.dotnetdud.blogspot.com
VBA Tips & Tricks (http://www.vbadud.blogspot.com)
|
|
Reply By:
|
codeFox
|
Reply Date:
|
4/16/2008 4:20:38 PM
|
Dear Shasur
Thanks again. We have almost got it. One thing I need to know are the three case statements just after "Select Case OrderDt" using dateDiff function. I assume I have to put some code after each case statement for different time periods?
Am I right?
Thanks
|
