$$Excel-Macros$$ welcome to order 2010.11.20.11.10.26
Hi Im ahmed saber. these days i bought 3 iphone 4G from a website : www.ebay365.org and i get the very great price and quality products from them . i want to introduce it to you . when you went their website and tell them my name ahmed saber . you will get a very great discount . the website is www.ebay365.org and dont forget to tell them my name ahmed saber. -- -- 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
Re: $$Excel-Macros$$ Need a MAXIF type formula
I need the max number in Col D on sheet 1 if the data in sheet 2 Col A-C is found in Sheet 1 Col A-C. From: ratu elissa To: excel-macros@googlegroups.com Sent: Fri, November 19, 2010 8:57:24 AM Subject: Re: $$Excel-Macros$$ Need a MAXIF type formula i don't really understand what you ask for On Fri, Nov 19, 2010 at 11:45 PM, None wrote: I need a MaxIf type of formula that will work with Excel 97 and Excel 2003. Thanks. > >I have two worksheets that have similar data in them for Col A-C. Sheet 1 has >numbers in col D. What I need in Col D on Sheet 2 is a formula that will find >the max of this column if the first three columns match. The result should be: >Sheet 2 cell D3 = 10 >Sheet 2 cell D4 = 10 >Sheet 2 cell D5 = 6 >Sheet 2 cell D6 = 7 > >Data: >Sheet 1: >Col A Col B Col C Col D >9944455123 AB123-8877445566 AB 1 >9944455777 AB123-8877445566 AB 5 >9944455123 AB123-2277445500 AC 10 >9944455123 AB123-8877445566 AB 4 >9944455123 AB123-8877445566 AB 4 >9944455123 AB123-8877445566 AB 2 >9944455123 AB123-8877445566 AB 1 >9944455123 AB123-8877445566 AB 3 >9944455123 AB123-8877445566 AB 10 >9944455777 AB123-8877445566 AB 7 >9944455111 AB123-2277445500 AC 5 >9944455123 AB123-8877445566 AB 4 >9944455123 AB123-8877445566 AB 8 >9944455123 AB123-8877445566 AB 1 >9944455123 AB123-8877445566 AB 5 >9944455123 AB123-8877445566 AB 10 >9944455123 AB123-2277445500 AC 4 >9944455123 AB123-8877445566 AB 4 >9944455123 AB123-2277445500 AC 2 >9944455111 AB123-2277445500 AC 1 >9944455123 AB123-2277445500 AC 3 >9944455777 AB123-8877445566 AB 1 >9944455123 AB123-8877445566 AB 5 >9944455123 AB123-8877445566 AB 5 >9944455123 AB123-8877445566 AB 4 >9944455123 AB123-8877445566 AB 8 >Sheet 2: >Col A Col B Col C Need formula here >9944455123 AB123-8877445566 AB >9944455123 AB123-2277445500 AC >9944455111 AB123-2277445500 AC >9944455777 AB123-8877445566 AB > >-- >-- > >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 > -- -- 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 -- -- 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
Re: $$Excel-Macros$$ Long-waited Excel VBA coaching program is live...its worth paying
Dear group, Thanks for overwhelming response on this program. Many of you have reviewed the program and subscribed it. Congratulations and happy Learning !! For those people who have still not reviewed the program , I encourage you to atleast visit the site and give me the feedback about the product. & yes the $100 dedicated coupon expiring soon in 2 hours... Please feel free to ask any questions you have. Thank you! Best regards, Ayush Jain Microsoft MVP Group Manager On Thu, Nov 18, 2010 at 11:17 AM, Ayush wrote: > Dear Group, > > I quite often receive queries from the members who want to learn VBA > macros but not finding resources to learn. > > There is a good news for such people. > > A Web based VBA Coaching Program is launched today by John Franco > ( Same person who shared his ebooks free for this group recently). > > I have personally reviewed the program which is designed in a way to > learn VBA quickly and easiest manner. The best part is the step by > step approach and numerous examples,videos which is very important for > a beginner. The learning from this program will definitely improve > your productivity and efficiency at work. > > I appreciate you as group member and created an early bird $100.00 > discount coupon you can use before Nov 19 - 23:59 US Eastern Time. > > Coupon: MMEARLYBIRD > > Act before the coupon expiresrush now... > > For more details about the cost and program details, Please visit:- > http://www.masterofmacros.com/affiliates/idevaffiliate.php?id=101 > > I encourage you to visit the link and have a look at the program > details. Feel free to get ask me any questions you have > > Thank you, > Ayush Jain > Microsoft MVP 2010 > -- -- 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
Re: $$Excel-Macros$$ Need a MAXIF type formula
i don't really understand what you ask for On Fri, Nov 19, 2010 at 11:45 PM, None wrote: > I need a MaxIf type of formula that will work with Excel 97 and Excel > 2003. Thanks. > > I have two worksheets that have similar data in them for Col A-C. Sheet 1 > has numbers in col D. What I need in Col D on Sheet 2 is a formula that > will find the max of this column if the first three columns match. The > result should be: > Sheet 2 cell D3 = 10 > Sheet 2 cell D4 = 10 > Sheet 2 cell D5 = 6 > Sheet 2 cell D6 = 7 > > Data: > Sheet 1: > Col A Col B Col C Col D 9944455123 AB123-8877445566 AB 1 9944455777 > AB123-8877445566 AB 5 9944455123 AB123-2277445500 AC 10 9944455123 > AB123-8877445566 AB 4 9944455123 AB123-8877445566 AB 4 9944455123 > AB123-8877445566 AB 2 9944455123 AB123-8877445566 AB 1 9944455123 > AB123-8877445566 AB 3 9944455123 AB123-8877445566 AB 10 9944455777 > AB123-8877445566 AB 7 9944455111 AB123-2277445500 AC 5 9944455123 > AB123-8877445566 AB 4 9944455123 AB123-8877445566 AB 8 9944455123 > AB123-8877445566 AB 1 9944455123 AB123-8877445566 AB 5 9944455123 > AB123-8877445566 AB 10 9944455123 AB123-2277445500 AC 4 9944455123 > AB123-8877445566 AB 4 9944455123 AB123-2277445500 AC 2 9944455111 > AB123-2277445500 AC 1 9944455123 AB123-2277445500 AC 3 9944455777 > AB123-8877445566 AB 1 9944455123 AB123-8877445566 AB 5 9944455123 > AB123-8877445566 AB 5 9944455123 AB123-8877445566 AB 4 9944455123 > AB123-8877445566 AB 8 > Sheet 2: > Col A Col B Col C Need formula here 9944455123 AB123-8877445566 AB > 9944455123 AB123-2277445500 AC 9944455111 AB123-2277445500 AC > 9944455777 AB123-8877445566 AB > > > -- > > -- > 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 > -- -- 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
$$Excel-Macros$$ Need a MAXIF type formula
I need a MaxIf type of formula that will work with Excel 97 and Excel 2003. Thanks. I have two worksheets that have similar data in them for Col A-C. Sheet 1 has numbers in col D. What I need in Col D on Sheet 2 is a formula that will find the max of this column if the first three columns match. The result should be: Sheet 2 cell D3 = 10 Sheet 2 cell D4 = 10 Sheet 2 cell D5 = 6 Sheet 2 cell D6 = 7 Data: Sheet 1: Col A Col B Col C Col D 9944455123 AB123-8877445566 AB 1 9944455777 AB123-8877445566 AB 5 9944455123 AB123-2277445500 AC 10 9944455123 AB123-8877445566 AB 4 9944455123 AB123-8877445566 AB 4 9944455123 AB123-8877445566 AB 2 9944455123 AB123-8877445566 AB 1 9944455123 AB123-8877445566 AB 3 9944455123 AB123-8877445566 AB 10 9944455777 AB123-8877445566 AB 7 9944455111 AB123-2277445500 AC 5 9944455123 AB123-8877445566 AB 4 9944455123 AB123-8877445566 AB 8 9944455123 AB123-8877445566 AB 1 9944455123 AB123-8877445566 AB 5 9944455123 AB123-8877445566 AB 10 9944455123 AB123-2277445500 AC 4 9944455123 AB123-8877445566 AB 4 9944455123 AB123-2277445500 AC 2 9944455111 AB123-2277445500 AC 1 9944455123 AB123-2277445500 AC 3 9944455777 AB123-8877445566 AB 1 9944455123 AB123-8877445566 AB 5 9944455123 AB123-8877445566 AB 5 9944455123 AB123-8877445566 AB 4 9944455123 AB123-8877445566 AB 8 Sheet 2: Col A Col B Col C Need formula here 9944455123 AB123-8877445566 AB 9944455123 AB123-2277445500 AC 9944455111 AB123-2277445500 AC 9944455777 AB123-8877445566 AB -- -- 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
$$Excel-Macros$$ Help required in importing word tables into excel file
I have this macro to extract tables from all the files in a particular
directory. However, this file takes only one table from a particular
file and only the first and the last column of the table.
I would request the power users to help in :
1) extracting all the tables in a particular file
2) The whole table from a file (ie., not just the first and last
collumn)
thanks.
Sub GetTablesFromWord()
'this Excel file must be in
'the same folder with the Word
'document files that are to be
'processed.
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim wTable As Word.Table
Dim wCell As Word.Cell
Dim basicPath As String
Dim fName As String
Dim myWS As Worksheet
Dim xlCell As Range
Dim lastRow As Long
Dim rCount As Long
Dim cCount As Long
Dim RLC As Long
Dim CLC As Long
basicPath = ThisWorkbook.Path & Application.PathSeparator
'change the sheet name as required
Set myWS = ThisWorkbook.Worksheets("Sheet1")
'clear any/all previous data on the sheet
myWS.Cells.Clear
'"open" Word
Set wApp = CreateObject("Word.Application")
'get first .doc file name in the folder
'with this Excel file
fName = Dir(basicPath & "*.doc*")
Do While fName <> ""
'this puts the filename into column A to
'help separate the table data in Excel
myWS.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
"FILE: [" & fName & "]"
'open the Word file
wApp.Documents.Open basicPath & fName
Set wDoc = wApp.Documents(1)
'if there is a table in the
'Word Document, work with it
If wDoc.Tables.Count > 0 Then
Set wTable = wDoc.Tables(1)
rCount = wTable.Rows.Count
cCount = wTable.Columns.Count
For RLC = 1 To rCount
lastRow = myWS.Range("A" & Rows.Count).End(xlUp).Row + 1
For CLC = 1 To cCount
'if there are merged cells in the
'Word table, an error will be
'generated - ignore the error,
'but also won't process the data
On Error Resume Next
Set wCell = wTable.Cell(RLC, CLC)
If Err <> 0 Then
Err.Clear
Else
If CLC = 1 Then
Set xlCell = myWS.Range("A" & lastRow)
xlCell = wCell
Else
Set xlCell = myWS.Range("B" & lastRow)
xlCell = wCell
End If
'trim chr$(13) chr$(7) from end of entry in Excel
If InStr(xlCell, Chr$(13)) > 0 Then
xlCell = Left(xlCell, InStr(xlCell, Chr$(13)) - 1)
End If
End If
On Error GoTo 0
Next
Next
Set wCell = Nothing
Set wTable = Nothing
End If ' end of wDoc.Tables.Count test
wDoc.Close False
Set wDoc = Nothing
fName = Dir() ' gets next .doc* filename in the folder
Loop
wApp.Quit
Set wApp = Nothing
MsgBox "Task 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
$$Excel-Macros$$ Purchase entry Macro
Hi Group,
I am not able to run the below macro, kindly help.
my requirement is like this.
Sheet 4 is having data in one single row, which creates 3 or 4
multiple rows with the help of below macro.
the below macro is only creates for the first line in sheet 4 only and
it is not able to create for other lines.
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.Offs
$$Excel-Macros$$ On Time
Hi, When I run the program excel starts flicekring how to avoid this. Thanks Chandra Shekar -- -- 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 ontime.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Purchase entry Macro from Sheet 4 to Sheet 1-
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.Offse
$$Excel-Macros$$ How to attach file
Hi, How to attach file in this group Thanks Chandra Shekar -- -- 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
$$Excel-Macros$$ plz help to automate diet chart
hi Guys plz help me to automate diet chart Regards, Lucky -- -- 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 diet chart.xls Description: MS-Excel spreadsheet
