Need help with mutilevel checks

codeFox · April 15th, 2008, 01:46 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

Shasur · April 15th, 2008, 08:49 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/a...for-excel.html

Here is a sample code

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)

codeFox · April 16th, 2008, 12:25 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.

Shasur · April 16th, 2008, 02:31 AM

Try if the following code helps you :

Code:

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)

codeFox · April 16th, 2008, 04:20 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