Thread: Shrink Formula
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Nigel Nigel is offline
external usenet poster
  
Posts: 923
Default Shrink Formula
Yes,
1. Remove all of the selections you do not need to select before putting
values into a cell - you will need to do this carefully

2. Similarly do not select worksheets, refer to them by prefixing them eg
Sheets(",3").Select
ActiveSheet.Unprotect Password:="susana"
Becomes
Sheets(",3").Unprotect Password:="susana"

3. Turn off the screen updating before the cells with
Application.Screenupdating = False, turn if on again at the end

Cheers
Nigel

"ANTONIO ATALA" wrote in message
...
Is there a way to shrink this 2 formulas

It is slowing down all my book

Tanks in advance

Antonio




Sub AñadirCliente2()


Answer = MsgBox("Cheque Primero si no se ha añadido este cliente a la
base de datos " & Chr(13) & Chr(13) & "Es este Cliente No.1 o No.2 ? .
Si
este cliente es numero 2 asegurese de poner el numero 2 al final del
nombre
y sin espacios" & Chr(13) & Chr(13) & "Esta seguro que quiere añadir a
este
Cliente a la Base de Datos ? ", _
vbYesNo + 256 + vbQuestion, "Hola Nelvita")
If Answer = vbNo Then Exit Sub ' the macro ends if the user selects the
CANCEL-button
Sheets("S").Select
ActiveSheet.Unprotect Password:="nelvita"
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[1]<1,"""",(R2C2-RC[-6])/7.15-RC[-1])"
Selection.Cells(1).Offset(0, 8) = .Range("I7")
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:="nelvita"
Sheets(",1").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",2").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",3").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",4").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",5").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",6").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",7").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",8").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",9").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",10").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",11").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets(",12").Select
ActiveSheet.Unprotect Password:="susana"
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:="susana"
Sheets("TARJETA DE CLIENTES").Select
MsgBox "Su Cliente a sido añadido satisfactoriamente"
End With

End Sub


Sub DeleteLastrow()
Sheets("S").Select
Answer = MsgBox(" Desea eliminar el ultimo cliente de la lista ? " &
Chr(13) & Chr(13) & "Este cliente se eliminara permanentemente" & Chr(13)
&
Chr(13) & " Para poner otraves este cliente a la base de datos" & Chr(13)
&
Chr(13) & " re-escriba toda su informacion a traves de la tarjeta de
clientes" & Chr(13) & Chr(13) & "Para eliminar este cliente oprima Si", _
vbYesNo, "Muebes de Mexico")
If Answer = vbNo Then Exit Sub ' the macro ends if the user selects the
CANCEL-button
Sheets(",1").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",2").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",3").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",4").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",5").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",6").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",7").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",8").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",9").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",10").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",11").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets(",12").Select
ActiveSheet.Unprotect Password:="susana"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="=S!B", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="susana"
Sheets("S").Select
ActiveSheet.Unprotect Password:="nelvita"
On Error Resume Next
last = ActiveSheet.Cells.Find(What:=" ", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Rows(last).Delete
ActiveSheet.Protect Password:="nelvita"
Sheets("TARJETA DE CLIENTES").Select
MsgBox "Su Cliente a sido eliminado satisfactoriamente"
On Error GoTo 0
End Sub














Reply With QuoteReply With Quote