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

Sub callingCode()

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

ActiveWorkbook.Sheets(1).Activate
myWriter.Init ActiveSheet, err

ActiveWorkbook.Sheets(2).Activate
myWriter.writeEntryToSource

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
Else
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
wks.Activate
cell.Select
cell.Offset(0, 3).Value = IIf(item.Debit <> 0, item.Debit, item.Credit)
Set cell = Nothing
End If
End If
Next
Next
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

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...