Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Check if name exist in a list
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default Check if name exist in a list

I need add a message box to Warn if a name already exist on a list and stop
the macro form executing

This is what I have

Anyone have any suggestions?



Thanks in advance



Sub AddCustomer()

'Answer = MsgBox(""Are you sure you like to add this customer to the
data base ? ", _

'vbYesNo + 256 + vbQuestion, "Muebles de México")

'If Answer = vbNo Then Exit Sub ' the macro ends if the user selects the
CANCEL-button

Application.StatusBar = "Please Wait!!.. Making a data base"

Application.ScreenUpdating = False

Sheets("S").Select

ActiveSheet.Unprotect Password:="NewsGroup"

Cells(Rows.Count, 2).End(xlUp)(2).Select

With Worksheets("TARJETA DE CLIENTES")

Selection.Cells(1) = .Range("B3")

Selection.Cells(1).Offset(0, -1) = "=R[-1]C+1"

Selection.Cells(1).Offset(0, 1) = .Range("A11")

Selection.Cells(1).Offset(0, 2) = .Range("h5")

Selection.Cells(1).Offset(0, 3) = .Range("H6")

Selection.Cells(1).Offset(0, 4) = "=SUM(RC[10]:RC[21])-RC[-1]+RC[22]"

Selection.Cells(1).Offset(0, 5) = "=SUM(RC[-3]-RC[-1])-RC[-2]"

Selection.Cells(1).Offset(0, 6) = "=IF(RC[2]<1,"""",RC[-2]/RC[2])"

Selection.Cells(1).Offset(0, 7) =
"=IF(RC[2]=""Semanas"",(R2C2-RC[-6])/7.15-RC[-1],IF(RC[2]=""Quincenas"",(R2C
2-RC[-6])/14.3-RC[-1],IF(RC[2]=""Mensualidades"",(R2C2-RC[-6])/28.06-RC[-1])
))"

'Selection.Cells(1).Offset(0, 7) =
"=IF(RC[1]<1,"""",(R2C2-RC[-6])/7.15-RC[-1])"

Selection.Cells(1).Offset(0, 8) = .Range("I7")

Selection.Cells(1).Offset(0, 9) = .Range("G7")

Selection.Cells(1).Offset(0, 11) = .Range("H3")

Selection.Cells(1).Offset(0, 12) = .Range("B4")

Selection.Cells(1).Offset(0, 13) = .Range("B8")

Selection.Cells(1).Offset(0, 14) = "=SUM(',1'!RC[-13]:RC[17])"

Selection.Cells(1).Offset(0, 15) = "=SUM(',2'!RC[-15]:RC[13])"

Selection.Cells(1).Offset(0, 16) = "=SUM(',3'!RC[-16]:RC[14])"

Selection.Cells(1).Offset(0, 17) = "=SUM(',4'!RC[-17]:RC[12])"

Selection.Cells(1).Offset(0, 18) = "=SUM(',5'!RC[-18]:RC[12])"

Selection.Cells(1).Offset(0, 19) = "=SUM(',6'!RC[-19]:RC[10])"

Selection.Cells(1).Offset(0, 20) = "=SUM(',7'!RC[-20]:RC[10])"

Selection.Cells(1).Offset(0, 21) = "=SUM(',8'!RC[-21]:RC[9])"

Selection.Cells(1).Offset(0, 22) = "=SUM(',9'!RC[-22]:RC[7])"

Selection.Cells(1).Offset(0, 23) = "=SUM(',10'!RC[-23]:RC[7])"

Selection.Cells(1).Offset(0, 24) = "=SUM(',11'!RC[-24]:RC[5])"

Selection.Cells(1).Offset(0, 25) = "=SUM(',12'!RC[-25]:RC[5])"

ActiveSheet.Protect Password:="NewsGroup"

Sheets(",1").Select

ActiveSheet.Unprotect Password:=" NewsGroup "

Cells(Rows.Count, 1).End(xlUp)(2).Select

Selection.Cells(1).Offset(0, 0) = "=S!RC[1]"

ActiveCell.FormulaR1C1 = "=S!RC[1]"

ActiveSheet.Protect Password:=" NewsGroup "

Application.ScreenUpdating = True

Application.StatusBar = False

MsgBox "Your Customer has been Added"

End With



End Sub



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Check if name exist in a list
Assuming the new customer name is in B3 of Tarjeta de Clientes

Sub AddCustomer()

'Answer = MsgBox(""Are you sure you like to add this customer to the
data base ? ", _

'vbYesNo + 256 + vbQuestion, "Muebles de México")

'If Answer = vbNo Then Exit Sub ' the macro ends if the user selects the
CANCEL-button

Application.StatusBar = "Please Wait!!.. Making a data base"

Application.ScreenUpdating = False

Sheets("S").Select

With Worksheets("TARJETA DE CLIENTES")
if application.Countif(Range("B:B"),.Range("B3")) < 0 then
msgbox "duplicate Clientes"
exit sub
End if
End With

ActiveSheet.Unprotect Password:="NewsGroup"

Cells(Rows.Count, 2).End(xlUp)(2).Select

With Worksheets("TARJETA DE CLIENTES")

Selection.Cells(1) = .Range("B3")

Selection.Cells(1).Offset(0, -1) = "=R[-1]C+1"

Selection.Cells(1).Offset(0, 1) = .Range("A11")

Selection.Cells(1).Offset(0, 2) = .Range("h5")

Selection.Cells(1).Offset(0, 3) = .Range("H6")

Selection.Cells(1).Offset(0, 4) = "=SUM(RC[10]:RC[21])-RC[-1]+RC[22]"

Selection.Cells(1).Offset(0, 5) = "=SUM(RC[-3]-RC[-1])-RC[-2]"

Selection.Cells(1).Offset(0, 6) = "=IF(RC[2]<1,"""",RC[-2]/RC[2])"

Selection.Cells(1).Offset(0, 7) =
"=IF(RC[2]=""Semanas"",(R2C2-RC[-6])/7.15-RC[-1],IF(RC[2]=""Quincenas"",(R2C
2-RC[-6])/14.3-RC[-1],IF(RC[2]=""Mensualidades"",(R2C2-RC[-6])/28.06-RC[-1])
))"

'Selection.Cells(1).Offset(0, 7) =
"=IF(RC[1]<1,"""",(R2C2-RC[-6])/7.15-RC[-1])"

Selection.Cells(1).Offset(0, 8) = .Range("I7")

Selection.Cells(1).Offset(0, 9) = .Range("G7")

Selection.Cells(1).Offset(0, 11) = .Range("H3")

Selection.Cells(1).Offset(0, 12) = .Range("B4")

Selection.Cells(1).Offset(0, 13) = .Range("B8")

Selection.Cells(1).Offset(0, 14) = "=SUM(',1'!RC[-13]:RC[17])"

Selection.Cells(1).Offset(0, 15) = "=SUM(',2'!RC[-15]:RC[13])"

Selection.Cells(1).Offset(0, 16) = "=SUM(',3'!RC[-16]:RC[14])"

Selection.Cells(1).Offset(0, 17) = "=SUM(',4'!RC[-17]:RC[12])"

Selection.Cells(1).Offset(0, 18) = "=SUM(',5'!RC[-18]:RC[12])"

Selection.Cells(1).Offset(0, 19) = "=SUM(',6'!RC[-19]:RC[10])"

Selection.Cells(1).Offset(0, 20) = "=SUM(',7'!RC[-20]:RC[10])"

Selection.Cells(1).Offset(0, 21) = "=SUM(',8'!RC[-21]:RC[9])"

Selection.Cells(1).Offset(0, 22) = "=SUM(',9'!RC[-22]:RC[7])"

Selection.Cells(1).Offset(0, 23) = "=SUM(',10'!RC[-23]:RC[7])"

Selection.Cells(1).Offset(0, 24) = "=SUM(',11'!RC[-24]:RC[5])"

Selection.Cells(1).Offset(0, 25) = "=SUM(',12'!RC[-25]:RC[5])"

ActiveSheet.Protect Password:="NewsGroup"

Sheets(",1").Select

ActiveSheet.Unprotect Password:=" NewsGroup "

Cells(Rows.Count, 1).End(xlUp)(2).Select

Selection.Cells(1).Offset(0, 0) = "=S!RC[1]"

ActiveCell.FormulaR1C1 = "=S!RC[1]"

ActiveSheet.Protect Password:=" NewsGroup "

Application.ScreenUpdating = True

Application.StatusBar = False

MsgBox "Your Customer has been Added"

End With



End Sub

--
Regards,
Tom Ogilvy

"Antonyo" wrote in message
...

I need add a message box to Warn if a name already exist on a list and
stop
the macro form executing

This is what I have

Anyone have any suggestions?



Thanks in advance



Sub AddCustomer()

'Answer = MsgBox(""Are you sure you like to add this customer to the
data base ? ", _

'vbYesNo + 256 + vbQuestion, "Muebles de México")

'If Answer = vbNo Then Exit Sub ' the macro ends if the user selects the
CANCEL-button

Application.StatusBar = "Please Wait!!.. Making a data base"

Application.ScreenUpdating = False

Sheets("S").Select

ActiveSheet.Unprotect Password:="NewsGroup"

Cells(Rows.Count, 2).End(xlUp)(2).Select

With Worksheets("TARJETA DE CLIENTES")

Selection.Cells(1) = .Range("B3")

Selection.Cells(1).Offset(0, -1) = "=R[-1]C+1"

Selection.Cells(1).Offset(0, 1) = .Range("A11")

Selection.Cells(1).Offset(0, 2) = .Range("h5")

Selection.Cells(1).Offset(0, 3) = .Range("H6")

Selection.Cells(1).Offset(0, 4) = "=SUM(RC[10]:RC[21])-RC[-1]+RC[22]"

Selection.Cells(1).Offset(0, 5) = "=SUM(RC[-3]-RC[-1])-RC[-2]"

Selection.Cells(1).Offset(0, 6) = "=IF(RC[2]<1,"""",RC[-2]/RC[2])"

Selection.Cells(1).Offset(0, 7) =

"=IF(RC[2]=""Semanas"",(R2C2-RC[-6])/7.15-RC[-1],IF(RC[2]=""Quincenas"",(R2C

2-RC[-6])/14.3-RC[-1],IF(RC[2]=""Mensualidades"",(R2C2-RC[-6])/28.06-RC[-1])
))"

'Selection.Cells(1).Offset(0, 7) =
"=IF(RC[1]<1,"""",(R2C2-RC[-6])/7.15-RC[-1])"

Selection.Cells(1).Offset(0, 8) = .Range("I7")

Selection.Cells(1).Offset(0, 9) = .Range("G7")

Selection.Cells(1).Offset(0, 11) = .Range("H3")

Selection.Cells(1).Offset(0, 12) = .Range("B4")

Selection.Cells(1).Offset(0, 13) = .Range("B8")

Selection.Cells(1).Offset(0, 14) = "=SUM(',1'!RC[-13]:RC[17])"

Selection.Cells(1).Offset(0, 15) = "=SUM(',2'!RC[-15]:RC[13])"

Selection.Cells(1).Offset(0, 16) = "=SUM(',3'!RC[-16]:RC[14])"

Selection.Cells(1).Offset(0, 17) = "=SUM(',4'!RC[-17]:RC[12])"

Selection.Cells(1).Offset(0, 18) = "=SUM(',5'!RC[-18]:RC[12])"

Selection.Cells(1).Offset(0, 19) = "=SUM(',6'!RC[-19]:RC[10])"

Selection.Cells(1).Offset(0, 20) = "=SUM(',7'!RC[-20]:RC[10])"

Selection.Cells(1).Offset(0, 21) = "=SUM(',8'!RC[-21]:RC[9])"

Selection.Cells(1).Offset(0, 22) = "=SUM(',9'!RC[-22]:RC[7])"

Selection.Cells(1).Offset(0, 23) = "=SUM(',10'!RC[-23]:RC[7])"

Selection.Cells(1).Offset(0, 24) = "=SUM(',11'!RC[-24]:RC[5])"

Selection.Cells(1).Offset(0, 25) = "=SUM(',12'!RC[-25]:RC[5])"

ActiveSheet.Protect Password:="NewsGroup"

Sheets(",1").Select

ActiveSheet.Unprotect Password:=" NewsGroup "

Cells(Rows.Count, 1).End(xlUp)(2).Select

Selection.Cells(1).Offset(0, 0) = "=S!RC[1]"

ActiveCell.FormulaR1C1 = "=S!RC[1]"

ActiveSheet.Protect Password:=" NewsGroup "

Application.ScreenUpdating = True

Application.StatusBar = False

MsgBox "Your Customer has been Added"

End With



End Sub





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Check for a tab if it is exist Farhad Excel Discussion (Misc queries) 2 December 21st 08 07:31 PM
Check if a value exist in a column DJ Steffo Excel Worksheet Functions 1 January 30th 06 02:37 PM
Check if pivot already exist Paul Wright Excel Programming 0 September 11th 03 05:45 AM
Check if pivot already exist Tom Ogilvy Excel Programming 0 September 11th 03 05:43 AM
check if a worksheet exist - VBA Warren Excel Programming 1 September 10th 03 05:02 AM


All times are GMT +1. The time now is 03:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement