Sub purchase_entry()
Dim mod_date As Date
Dim entry_date As Date
Dim reference As String
Dim description As String
Dim period As String
Dim amount As Long
Dim locatio As String
Dim coa As String
Dim coa_code As String
Dim tax_account As String
Dim tax_account1 As String
Dim tax_account_name As Variant
Dim lookfor As Range
Dim rng As Range
Dim col As Integer
Dim tax_amount As Long
Dim tax_amount1 As Long
Dim tot_amt As Long
'to be fix
Set lookfor = Sheets("sheet1").Range("e3")
Set rng = Sheets("sheet5").Columns("A:B")
col = 2
period = InputBox("Enter Period")
Worksheets("sheet1").Select
Range("A2").Select
ActiveCell.Value = Int(Now())
ActiveCell.Offset(0, 1) = "GENJV"
Worksheets("sheet4").Select
Range("A2").Select
reference = Right(ActiveCell.Offset(0, 32), 10)
description = ActiveCell.Offset(0, 38)
amount = ActiveCell.Offset(0, 46)
tax_account_name = Application.VLookup(lookfor, rng, col, 0)
If ActiveCell.Offset(0, 0) = "101" Then
Location = "WMAH04"
ElseIf ActiveCell.Offset(0, 0) = "201" Then
Location = "NHAR01"
End If
coa = ActiveCell.Offset(0, 10)
coa_code = ActiveCell.Offset(0, 9)
If ActiveCell.Offset(0, 44) = "CST" Then
tax_account = "10301004"
tax_amount = ActiveCell.Offset(0, 47).Value
ElseIf ActiveCell.Offset(0, 44) = "VAT" And ActiveCell.Offset(0,
45) = 1 Then
tax_account = "10304007"
tax_amount = ActiveCell.Offset(0, 48).Value
ElseIf ActiveCell.Offset(0, 44) = "VAT" And ActiveCell.Offset(0,
45) = 5 Then
tax_account = "10304038"
tax_amount = ActiveCell.Offset(0, 48).Value
ElseIf ActiveCell.Offset(0, 44) = "VAT" And ActiveCell.Offset(0,
45) = 5.25 Then
tax_account = "10304038"
tax_amount = ActiveCell.Offset(0, 48).Value
tax_account1 = "10304039"
tax_amount1 = ActiveCell.Offset(0, 49).Value
ElseIf ActiveCell.Offset(0, 44) = "VAT" And ActiveCell.Offset(0,
45) = 12.5 Then
tax_account = "10304006"
tax_amount = ActiveCell.Offset(0, 48).Value
ElseIf ActiveCell.Offset(0, 44) = "VAT" And ActiveCell.Offset(0,
45) = 13.13 Then
tax_account = "10304038"
tax_amount = ActiveCell.Offset(0, 48).Value
tax_account1 = "10304040"
tax_amount1 = ActiveCell.Offset(0, 49).Value
Else
tax_account = "check code"
End If
tot_amt = amount + tax_amount + tax_amount1
Worksheets("sheet4").Select
Range("A2").Select
Do Until IsEmpty(ActiveCell)
Worksheets("sheet1").Select
Range("A2").Select
ActiveCell.Offset(0, 2) = reference
ActiveCell.Offset(0, 3) = description
ActiveCell.Offset(0, 4) = "10301001"
ActiveCell.Offset(0, 5) = "STOCK AT WAREHOUSE"
ActiveCell.Offset(0, 6) = "A"
ActiveCell.Offset(0, 7) = period
ActiveCell.Offset(0, 8) = amount
ActiveCell.Offset(0, 9) = "D"
' to be fix
ActiveCell.Offset(0, 11) = "MER01"
ActiveCell.Offset(0, 12) = Location
ActiveCell.Offset(0, 14) = coa
ActiveCell.Offset(0, 15) = coa_code
ActiveCell.Offset(1, 0) = Int(Now())
ActiveCell.Offset(1, 1) = "GENJV"
ActiveCell.Offset(1, 2) = reference
ActiveCell.Offset(1, 3) = description
ActiveCell.Offset(1, 4) = tax_account
ActiveCell.Offset(1, 5) = tax_account_name
ActiveCell.Offset(1, 6) = "A"
ActiveCell.Offset(1, 7) = period
ActiveCell.Offset(1, 8) = tax_amount
ActiveCell.Offset(1, 9) = "D"
'to be fix
ActiveCell.Offset(1, 11) = "MER01"
ActiveCell.Offset(1, 12) = Location
ActiveCell.Offset(1, 14) = coa
ActiveCell.Offset(1, 15) = coa_code
Worksheets("sheet4").Select
If ActiveCell.Offset(0, 49).Value = 0 Then
Worksheets("sheet1").Select
ActiveCell.Offset(2, 0) = Int(Now())
ActiveCell.Offset(2, 1) = "GENJV"
ActiveCell.Offset(2, 2) = reference
ActiveCell.Offset(2, 3) = description
ActiveCell.Offset(2, 4) = coa_code
ActiveCell.Offset(2, 5) = coa
ActiveCell.Offset(2, 6) = "A"
ActiveCell.Offset(2, 7) = period
ActiveCell.Offset(2, 8) = tot_amt
ActiveCell.Offset(2, 9) = "C"
'to be fix
ActiveCell.Offset(2, 11) = "MER01"
ActiveCell.Offset(2, 12) = Location
ActiveCell.Offset(2, 14) = "STOCK AT WAREHOUSE"
ActiveCell.Offset(2, 15) = "10301001"
Else
Worksheets("sheet1").Select
ActiveCell.Offset(2, 0) = Int(Now())
ActiveCell.Offset(2, 1) = "GENJV"
ActiveCell.Offset(2, 2) = reference
ActiveCell.Offset(2, 3) = description
ActiveCell.Offset(2, 4) = tax_account1
ActiveCell.Offset(2, 5) = tax_account_name
ActiveCell.Offset(2, 6) = "A"
ActiveCell.Offset(2, 7) = period
ActiveCell.Offset(2, 8) = tax_amount1
ActiveCell.Offset(2, 9) = "D"
'to be fix
ActiveCell.Offset(2, 11) = "MER01"
ActiveCell.Offset(2, 12) = Location
ActiveCell.Offset(2, 14) = coa
ActiveCell.Offset(2, 15) = coa_code
ActiveCell.Offset(3, 0) = Int(Now())
ActiveCell.Offset(3, 1) = "GENJV"
ActiveCell.Offset(3, 2) = reference
ActiveCell.Offset(3, 3) = description
ActiveCell.Offset(3, 4) = coa_code
ActiveCell.Offset(3, 5) = coa
ActiveCell.Offset(3, 6) = "A"
ActiveCell.Offset(3, 7) = period
ActiveCell.Offset(3, 8) = tot_amt
ActiveCell.Offset(3, 9) = "C"
'to be fix
ActiveCell.Offset(3, 11) = "MER01"
ActiveCell.Offset(3, 12) = Location
ActiveCell.Offset(3, 14) = "STOCK AT WAREHOUSE"
ActiveCell.Offset(3, 15) = "10301001"
End If
Loop
MsgBox "completed"
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
