Check if name exist in a list

A

Antonyo

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
 
T

Tom Ogilvy

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 said:
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"",(R2C2-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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use Solver in Excel VBA 1
Geometric progression in VBA 10
Dynamic add formula 2
Shrink Formula 1
Data samples 3
Modify The Code For Importing Text File into an Excel File 5
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Macro autofills formula when a cell value is changed 4

Top