Previous Topic Next Topic

Auto filling bill number and date

Solved/Closed
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014 - Mar 17, 2014 at 07:37 PM
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014 - May 7, 2014 at 10:58 AM
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
Related:

16 responses

Answer 1 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 19, 2014 at 01:10 AM
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
Answer 2 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 20, 2014 at 02:20 AM
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
Answer 3 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 18, 2014 at 03:15 AM
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
0
Answer 4 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
Mar 18, 2014 at 11:26 PM
https://www.dropbox.com/s/3o1b6xvpkqo4prf/acc.xlsx

this is my work book please can you check this
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
Mar 19, 2014 at 01:16 PM
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
0
Answer 6 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 20, 2014 at 01:23 AM
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
0
Answer 7 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
Mar 20, 2014 at 02:13 AM
Ok then can we make a small change end with instated of base input sheat as the last sheet
0
Answer 8 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
Mar 20, 2014 at 02:35 AM
input sheet to be last sheet instead of last sheet then next entry will be easy
0
Answer 9 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 20, 2014 at 03:45 AM
"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????????
0
Answer 10 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
Mar 22, 2014 at 01:43 AM
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
0
Answer 11 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 22, 2014 at 04:12 AM
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
0
Answer 12 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
Mar 22, 2014 at 04:46 AM
THANKS THANKS
0
Answer 13 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 22, 2014 at 06:33 AM
welcome
0
Answer 14 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
Mar 25, 2014 at 03:42 AM
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
0
Answer 15 / 16
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 25, 2014 at 09:47 AM
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
0
Answer 16 / 16
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014
May 7, 2014 at 10:58 AM
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
0