Results 1 to 3 of 3

Thread: All the code

  1. #1
    Join Date
    Oct 2003

    Unanswered: All the code

    I had to made some changes related to activating sheets but this works. What a was thinking about for the String enums was for the Acct#s i will have about 120 of these.

    Another thing i just realized is the find wont work becuase the * is used in the Acct#s. So i may just have to check value to value
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003

    the code

    Sub callingCode()

    Dim myWriter As New jeWriter
    Set myWriter = New jeWriter
    Dim err As String
    '************************************************* ***********
    '*** PROBLEM!!!! CAN NOT USE '*' in account #
    '************************************************* ********

    myWriter.Init ActiveSheet, err


    End Sub

    jeWriter Class
    Option Explicit
    Private Entries As New Collection
    'Public vMatrix As Variant
    Public Sub Init(wks As Worksheet, ByRef err As String)

    Dim je As JournalEntry
    Set je = New JournalEntry
    Set Entries = New Collection

    If isValidJeWks(wks, err) Then '
    Dim var As Variant
    var = GetData(wks)
    ' GetData wks
    If True Then 'ValidEntires(var, err)
    Set Entries = je.GetEntries(var) 'var
    End If
    MsgBox err
    End If
    End Sub
    Function GetData(wks As Worksheet) As Variant

    Dim vMatrix As Variant
    Dim rngLastRow As Range

    With wks.Range("A:C")

    Set rngLastRow = .Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

    If Not rngLastRow Is Nothing Then
    vMatrix = .Resize(rngLastRow.Row - .Row + 1, .Columns.Count)
    End If

    End With

    GetData = vMatrix

    End Function
    Sub writeEntryToSource()

    Dim cell As Range
    Dim wks As Worksheet
    Dim item As JournalEntry

    UpdateScreenDisplayAlertsEnableEvents (False)

    For Each item In Entries
    For Each wks In ActiveWorkbook.Sheets
    Set cell = wks.Cells.Find(What:=item.AcctNo, After:=ActiveCell _
    , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Not cell Is Nothing Then
    If wks.Name <> "JE" Then
    cell.Offset(0, 3).Value = IIf(item.Debit <> 0, item.Debit, item.Credit)
    Set cell = Nothing
    End If
    End If
    UpdateScreenDisplayAlertsEnableEvents (True)
    End Sub
    Private Sub UpdateScreenDisplayAlertsEnableEvents(trueFalse As Boolean)
    With Application
    .ScreenUpdating = trueFalse
    .DisplayAlerts = trueFalse
    .EnableEvents = trueFalse
    End With
    End Sub
    Public Function isValidJeWks(wks As Worksheet, ByRef err) As Boolean

    err = vbNullString

    If wks.UsedRange.Columns.Count <> 3 Then
    err = "Invalid Used Columns, the file: " & wks.Name & " has " & wks.UsedRange.Columns.Count & " Columns Not 3"
    End If
    If UCase(wks.Range("A1").Value) <> "ACCOUNT NO." Then
    err = err & vbCrLf & "Cell A1 has a value of " & wks.Range("A1").Value & " Not AccountNo"
    End If
    If UCase(wks.Range("B1").Value) <> "DEBIT" Then
    err = err & vbCrLf & "Cell A1 has a value of " & wks.Range("b1").Value & " Not Debit"
    End If
    If UCase(wks.Range("C1").Value) <> "CREDIT" Then
    err = err & vbCrLf & "Cell A1 has a value of " & wks.Range("b1").Value & " Not Credit"
    End If
    isValidJeWks = CBool((Len(err) = 0))

    End Function


    JournalEntry Class
    'class fields
    Private Entries As New Collection

    Private mDebit As Integer
    Private mCredit As Integer
    Private mAcctNo As String

    Private mAcctType As AcctType
    Private mDivision As Division

    Public Enum AcctType
    Asset = 1
    Liability = 2
    Equity = 3
    Income = 4
    Exp = 5
    End Enum

    Public Enum Division
    Combined = 1
    TotalCore = 2
    Excess = 3
    WC = 4
    D_O = 5
    AllOtherLines = 6
    Prop = 7
    OE = 23
    End Enum
    'Read properties
    Public Property Let Debit(val As Integer)
    mDebit = val
    End Property
    Public Property Get Debit() As Integer
    Debit = mDebit
    End Property

    Public Property Let Credit(val As Integer)
    mCredit = val
    End Property
    Public Property Get Credit() As Integer
    Credit = mCredit
    End Property

    Public Property Let AcctNo(val As String)
    mAcctNo = val
    End Property
    Public Property Get AcctNo() As String
    AcctNo = mAcctNo
    End Property

    Public Property Let AccountType(val As AcctType)
    mAcctType = val
    End Property
    Public Property Get AccountType() As AcctType
    AccountType = mAcctType
    End Property

    Public Property Let Div(val As Division)
    mDivision = val
    End Property
    Public Property Get Div() As Division
    Div = mDivision
    End Property

    Public Function GetEntries(data As Variant) As Collection
    Dim i As Integer

    For i = LBound(data, 1) + 1 To UBound(data, 1)
    AddEntry data(i, 1), data(i, 2), data(i, 3)
    Next i
    Set GetEntries = Entries
    End Function
    Private Sub AddEntry(entry1 As Variant, entry2 As Variant, entry3 As Variant)

    Dim je As JournalEntry
    Set je = New JournalEntry

    je.AcctNo = entry1
    je.Debit = entry2
    je.Credit = entry3

    Entries.Add je

    End Sub
    Last edited by mikezx10; 05-22-09 at 07:15.

  3. #3
    Join Date
    Sep 2008
    London, UK
    Hi Mike,

    Sorry, there's quite a lot of code here. Can you point out exactly what the problem is?

    I think I'm mostly struggling with what the goal is. At the moment the code seems to be taking the debit + credit values from the JE sheet and putting them in as adjustments for the relevant accounts on the OutPut sheet. But this can be done using formulas?

    Hope that helps...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts