VBA Macro help needed

[Vijay DSK]

Hi all,
Thanks once again for the help rendered by this forum.
Once again its time for me to ask an other question.

The question is
I have 2 excel sheets. In sheet1 (Name is Main), i have customer name in A1
and customer no in A2, B1 will carry the customer name and B2 will carry the
customer no.

What I am looking at is ........
Any entry punched in the cells (B1 & B2) should be copied on to sheet 2 of
A2 and B2. Like wise any value punched in those cells should be copied on to
A3 and B3 and so on....
An other point is the vba should give a message "Contact Branch Manager" if
any duplicate entry is added.

Hope I am clear in asking the question.

Thanks once again.

 

[Harald Staff]

Hi

Worksheets are not ideal for data entry like this. I suggest you create a
userform. See
http://www.contextures.com/xlUserForm01.html
on how to get started.

Best wishes Harald

 

[ND Pard]

Appears that all you need to do is a Copy followed by a Paste-Special |
Transpose

Good Luck.

 

[Vijay DSK]

Yes ND Pard
You are right. But the thing is it should be done with the help of VBA and
the change is not time based. Hence what I am looking what ever new thing
appears in those cells, they should be copied and pasted in the other sheet.

And also if any duplicate entry appears it should give a messae box stating
that "Contact your branch manger".

Hope I am clear.

Harald:
I am looking for a excel sheet only, but not for the user forms.

 

[keiji kounoike]

I don't know this is what you want, but try this one.

copy and paste the following code to your Main sheet's module.
Click cells in column A or B or enter data in Main sheet. then, data in
Main sheet would be copied in sheet2 in this case.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmp As Range
Dim Destsh As Worksheet

Application.EnableEvents = False

'Data in Main sheet will be transfer to destsh
Set Destsh = Worksheets("Sheet2") 'Change to your sheet's name

On Error Resume Next
If Target.Value = "" Then
Application.EnableEvents = True
Exit Sub
End If

Set tmp = Destsh.Columns("A:B").Find(Target.Value, _
LookIn:=xlValues, lookat:=xlWhole)

On Error GoTo 0
If Not tmp Is Nothing Then
MsgBox Target.Value & ":already exist. Contact your branch manager"
Exit Sub
End If

Select Case Target.Column Mod 2

Case 0
Set tmp = Destsh.Cells(Cells.Rows.Count, "B").End(xlUp)
If tmp <> "" Then
tmp.Offset(1, 0) = Target.Value
Else
tmp = Target.Value
End If

Case 1
Set tmp = Destsh.Cells(Cells.Rows.Count, "A").End(xlUp)
If tmp <> "" Then
tmp.Offset(1, 0) = Target.Value
Else
tmp = Target.Value
End If

Case Else

End Select

Application.EnableEvents = True

End Sub

keiji

 

[Vijay DSK]

Keiji,
Thanks for your time.

Theoritically, this is what I am looking at. But practically, 2 errors i
obsereved, request you to rectify them.

1. The code was copying to column b of sheet 2 (ex. b1,b2,b3,...) but what i
am looking is they should be copied into A2 and B2, next entry in A3 and B3
and so on..
2.When duplicate record is found in the sheet 2 it was giving the message
box as required and after that any new/old entry entered in the cells, it was
not executing the code and after that no copying is also happening.

Pls look into this.

Hope i am clear in asking.

 

[keiji kounoike]

Sorry for having misread your post. but I'm still not clear what you are
looking for. you mean your data are always entered in B1 and B2 in Main
sheet?
when duplicate record was found, the record should be ignored or should
be copied to sheet2 as it is. what does duplicate means? Is same
customer name and different id duplicate?

Following is my rectified code. but not sure rectified as you think.
when duplicate was found, this code did nothing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmp As Range
Dim Destsh As Worksheet

Application.EnableEvents = False

'Data in Main sheet will be transfer to destsh
Set Destsh = Worksheets("Sheet2") 'Change to your sheet's name

On Error Resume Next
If Application.Intersect(Target, Range("b1:B2")) Is Nothing Or _
(Range("B1") = "" Or Range("B2") = "") Then
Application.EnableEvents = True
Exit Sub
End If

Set tmp = Destsh.Columns("A").Find(Range("B1").Value, _
LookIn:=xlValues, lookat:=xlWhole)

On Error GoTo 0
If Not tmp Is Nothing Then
If Target.Address = "$B$2" Then
tmp.Offset(0, 1) = Range("B2")
Else
MsgBox Range("B1") & _
" already exist. Contact your branch manager"
End If
Application.EnableEvents = True
Exit Sub
End If

Set tmp = Destsh.Cells(Cells.Rows.Count, "A").End(xlUp)

If tmp <> "" Then
tmp.Offset(1, 0) = Range("B1")
tmp.Offset(1, 1) = Range("B2")
Else
tmp = Range("B1")
tmp.Offset(0, 1) = Range("B2")
End If

Application.EnableEvents = True

End Sub

 

[Vijay DSK]

Keiji,
You are right. My data always be in B1 and B2, after punching the data it
should be copied on to sheet2. When same entry based on Customer Id is typed
the message should be displayed and shall not be allowed to copy on to the
sheet 2.

Hope I am clear on this.

 

[keiji kounoike]

Then check this one. I think there still have some not satisfy your demand.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmp As Range
Dim Destsh As Worksheet

Application.EnableEvents = False

'Data in Main sheet will be transfer to destsh
Set Destsh = Worksheets("Sheet2") 'Change to your sheet's name

On Error Resume Next
If Application.Intersect(Target, Range("b1:B2")) Is Nothing Or _
(Range("B1") = "" Or Range("B2") = "") Then
Application.EnableEvents = True
Exit Sub
End If

Set tmp = Destsh.Columns("B").Find(Range("B2").Value, _
LookIn:=xlValues, lookat:=xlWhole)

On Error GoTo 0
If Not tmp Is Nothing Then
If Target.Address = "$B$1" Then
tmp.Offset(0, -1) = Range("B1")
Else
MsgBox Range("B2") & _
" already exist. Contact your branch manager"
End If
Application.EnableEvents = True
Exit Sub
End If

Set tmp = Destsh.Cells(Cells.Rows.Count, "A").End(xlUp)

If tmp <> "" Then
tmp.Offset(1, 0) = Range("B1")
tmp.Offset(1, 1) = Range("B2")
Else
tmp = Range("B1")
tmp.Offset(0, 1) = Range("B2")
End If

Application.EnableEvents = True

End Sub

keiji

 

[Vijay DSK]

Keiji,
No this code is not working according to my requirement. The previous one
was in line with my requirement. How we could stick to that one and modify
according to my post.

Seems you may need some clarification regarding what i am looking in the
code. If yes, you can write me at vijaydsk at live dot com

 

[Andrew]

Hi all,
Thanks once again for the help rendered by this forum.
Once again its time for me to ask an other question.

The question is
I have 2 excel sheets. In sheet1 (Name is Main), i have customer name in A1
and customer no in A2, B1 will carry the customer name and B2 will carry the
customer no.

What I am looking at is ........
Any entry punched in the cells (B1 & B2) should be copied on to sheet 2 of
A2 and B2. Like wise any value punched in those cells should be copied onto
A3 and B3 and so on....
An other point is the vba should give a message "Contact Branch Manager" if
any duplicate entry is added.

Hope I am clear in asking the question.

Thanks once again.

The required behavior of your sheet is not very clearly stated here.
It sounds like you want the values from sheet 1 to be copied onto
sheet 2, and then you want a warning if there are duplicates, even
though you are creating duplicates. Is this for data storage? Are
you collecting/compiling customer information? Or is this for a
single customer, like an invoice? I don't understand. But I do agree
that something of this sort is best done using a user input form. You
can call it with an on screen button. The form can have a button
called "submit" or something like that. And the code for the "submit"
button could scan for duplicates and send warnings, or whatever you
want it to do.