Auto filling bill number and date [Solved/Closed]

Report
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
-
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
-
hi i need a macro for my office use

the condition is like this
sheat one input
NAME
CUSTOMER NUMBER 17 Narayanan{[=INDEX(BASE!B:B,MATCH(G6,BASE!A:A,0))BASE]!B:B,MATCH(G6,BASE!A:A,0))}
DATE 7/3/2014
BILL NUMBER 147

sheat two base

001 Noorudeen
002 Brees Willa
003 Shereefa
004 JAMEEL
005 Muhammad Fisal
006 Hasinar
007 Fathibi
008 Saibunneesa
009 Jabbar
010 NEW
011 Ramakrishnan
012 Sawdamini Teacher
013 Remeshan Mash Near saudamini teacher
014 NEW
015 NEW
016 Vimod Kumar
017 Narayanan

now i need this the date shuld come to c17 and bill number shuld come to d17
if customer number is 4 the date shuld go to c4 bill number shuld come to d4

16 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
download the workbook form this web page

http://speedy.sh/cGF3s/JOWEL-30319-acc.xlsm

RIGHT CLICK SHEET "INPUT" and click view code
you can see the event code
(alternately open vbeditor (a't+F11) you will see the code. at top you can see that this is code for sheet1 because input is the first sheet.

you need not do anything (once you fill up the data in sheet "input"0 except you have to DOUBLE CLICK G6 IN INPUT SHEET.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
not clear to me.
do u want input sheet to be last sheet instead of firsts sheet

I suppose that does not make any change. if wherever input sheet is right click that tab and copy the code

in this present file wherever you shift the input sheet by clicking the tab and dragging to second place the code will be there (check this by right clicking tab from the new location and clinking view code)
make another expt open vb editor and hit control+R the sheet will continue to be called as sheet1.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
sheet is like this from A1
NAME Narayanan
CUSTOMER NUMBER 17 doublecllick B2
DATE 7/3/2014
BILL NUMBER 147

sheet 2 is like this

cust nr name
1 Noorudeen
2 Brees Willa
3 Shereefa
4 JAMEEL
5 Muhammad Fisal
6 Hasinar
7 Fathibi
8 Saibunneesa
9 Jabbar
10 NEW
11 Ramakrishnan
12 Sawdamini Teacher
13 Remeshan Mash Near saudamini teacher
14 NEW
15 NEW
16 Vimod Kumar
17 Narayanan


right click TAB of sheet 2 and click view code
in the window that comes up copy this event code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim nname As String, ddate As Date, billnr As Long, custnr As Long, cfind As Range
Cancel = True
If Target.Address <> "$B$2" Then GoTo enableevents
If Target = "" Then GoTo enableevents
On Error GoTo enableevents
Application.enableevents = False
With Worksheets("sheet1")
nname = .Range("B1")
custnr = Target
ddate = .Range("B3")
billnr = .Range("B4")
End With
With Worksheets("sheet2")

Set cfind = .Columns("A:A").Find(what:=custnr, lookat:=xlWhole)
If Not cfind Is Nothing Then
If Trim(cfind.Offset(0, 1)) = nname Then
cfind.Offset(0, 2) = Format(ddate, "m/d/yy")
cfind.Offset(0, 3) = billnr
End If
End If
.Activate
End With
MsgBox "macro done. check sheet2"
enableevents:
Application.enableevents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)



End Sub


SAVE THE FILE AS MACRO ENABLED FILE

NOW DOUBLE CLICK SHEET1 B2 REPEAT SHEET1 B2
SEE WHAT HAPPENS.

remember when you make entries do not add unnecessary spaces either. for e.g. in sheet narayanan has a space at the end. The name and other data in sheet 1 and sheet2 must be the same
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

https://www.dropbox.com/s/3o1b6xvpkqo4prf/acc.xlsx

this is my work book please can you check this
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

now everything working perfectly but i need a small help
instead of double click in g6
after entering all data like date and bill number
normally we will hit the enter button then the code should run and courser should go to g6
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
As far as I know (I am not an expert) it is not possible to write a macro (even if this macro may be assigned to the button) to fire a doubleclick on a cell. I wonder what is the great difference in clicking a button or double clicking a cell.

I do not want to use "selection_change" or "change" event code because then there is a complication if your first enter G6 then that event code will be immediately fired giving a wrong answer. This means you have to be extremely careful to enter G6 only last after filling G7 and G8. This will be extremely difficult unless you are very careful

Better accept double click G6 in which case in any order you can fill up data in G6,G7 and G8.


did you try double digit number in G6 like 15 or 16 did it work if not revert back to newsgroup
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

Ok then can we make a small change end with instated of base input sheat as the last sheet
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

input sheet to be last sheet instead of last sheet then next entry will be easy
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
"LAST sheet to LAST sheet. "
some spelling mistake

putting input sheet as last sheet does not solve the problem

If I use "selection_change" or "change" event code this is not the problem
in INPUT SHEET

the cells G7 and G8 must be filled first and only G6 last.
even here as soon as you select G6 to fill the customer number, the event code will be fired giving some probem or bug in the case of selection_change event
but "change" event is fired only after you enter something in G6 and then hit enter key. You must study some more on the EVENT CODES.

is it possible for you or your team member or user to be so careful????????
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

https://www.dropbox.com/s/iohw4dmqhxstkoq/jowel111.xlsm

I TRIED TO UPDATE THE MACRO BUT UNFORTUNATELY IT IS NOT WORKING CAN YOU EDIT THIS THING
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
you did small two slips

formerly there were two sheets now only one sheet that the name is not input but sheet1

see modified event code below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim nname As String, ddate As Date, billnr As Long, custnr As Long, cfind As Range
Cancel = True

If Target.Address <> "$D$1" Then GoTo enableevents
If Target = "" Then GoTo enableevents
On Error GoTo enableevents
Application.enableevents = False
With Worksheets("sheet1")
nname = .Range("F1")
custnr = "00" & Target
ddate = .Range("D2")
billnr = .Range("D3")
'End With
'With Worksheets("sheet1")

Set cfind = .Columns("G:G").Find(what:=custnr, lookat:=xlWhole)
If Not cfind Is Nothing Then
If Trim(cfind.Offset(0, 1)) = nname Then
cfind.Offset(0, 2) = Format(ddate, "m/d/yy")
cfind.Offset(0, 3) = billnr
End If
End If
.Activate
End With
MsgBox "macro done. check sheet1 columns I and J"
enableevents:
Application.enableevents = True

End Sub
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

THANKS THANKS
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
welcome
Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

https://www.dropbox.com/s/ca7i7n9zj4c4315/test.xlsm



can you give me some help please


before pasting the data it should check the box A3 and paste the value to correspondent row
example A3 have 1/14 then data shuld paste in I and J
if A3 have 2/14 then data shuld paste in K and L
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
event code modified

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim nname As String, amount As Long, billnr As Long, custnr As Long, cfind As Range, cfind1 As Range
Dim r2 As Range
Cancel = True

If Target.Address <> "$D$1" Then GoTo enableevents
If Target = "" Then GoTo enableevents
On Error GoTo enableevents
Application.enableevents = False
With Worksheets("sheet1")
Set r2 = Range(.Range("I1"), .Cells(1, Columns.Count).End(xlToLeft))
Set cfind1 = r2.Find(what:=Range("A3"), lookat:=xlWhole)
nname = .Range("F1")
custnr = "00" & Target
amount = .Range("D2")
billnr = .Range("D3")
End With
With Worksheets("sheet1")

Set cfind = .Columns("G:G").Find(what:=custnr, lookat:=xlWhole)
If Not cfind Is Nothing Then
If Trim(cfind.Offset(0, 1)) = nname Then
.Cells(cfind.Row, cfind1.Column) = amount
.Cells(cfind.Row, cfind1.Column + 1) = billnr
'cfind.Offset(0, 2) = amount
'cfind.Offset(0, 3) = billnr
End If
End If
.Activate
End With
enableevents:
Application.enableevents = True

End Sub




Posts
10
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014

hi sir
this is my account ledger
https://www.dropbox.com/s/5yxmj8zt3oksjv7/final.xlsm

in this if i pressed the save button in SHEET 4 ( EDIT CUST )
check j32 and
j33 to in sheet 1 (input) need to check the corespondent serial number and past to U

j34 THIS IN Q
j35 THIS IN R
j36 THIS IN S
j37 to in sheet 11 (inp) v
j38 this in w
j39 this in x


will you help me to make this code please

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!