Visual Basic para Aplicaciones - Eficientizar mi codigo???

Life is soft - evento anual de software empresarial
   
Vista:

Eficientizar mi codigo???

Publicado por Jose Luis Rdz (6 intervenciones) el 13/04/2009 06:07:57
Queria ver sugerencias para hacer mi codigo mas eficiente ... debido a que marca muchos errores, como no encontrar una celda o operador en este caso...
aqui va el condigo... es algo largo y segun yo lo acorte si es necesario el archivo se los mando a su correo....

Option Explicit
Public rng As Range
Public X As Long
Public Y As Long
Public E As Integer
Public Fecha As Date
Public Respuesta As Integer
Public TxtArt As Control
Public TxtProd As Control
Public celda As Range


Public Sub CommandButton2_Click()
Dim ArrayMrcP As Variant
Dim ArrayMrcA As Variant
Dim ArrayNC As Variant
Dim Ptext As Control
Dim Atext As Control
Dim CCbox As Control
Dim MrcA As Control
Dim MrcP As Control
Dim ArrayCbox As Variant
Dim ArrayTxt As Variant
Dim ArrayTArt As Variant
Dim ArraySCbox As Variant
Dim ArraySTxt As Variant
Dim ArraySNC As Variant
Dim ArraySTArt As Variant
Dim MMaq As String

ArrayMrcP = Array("Marc62p", "Marc63p", "Marc64p", "MarcS1p", "MarcS2p", "MarcFOp", "Marc312p", "MarcHp", "MarcBnchrp", "MarcReunp")
ArrayMrcA = Array("Marc62a", "Marc63a", "Marc64a", "MarcS1a", "MarcS2a", "MarcFOa", "Marc312a", "MarcHa", "MarcBnchra", "MarcReuna")
ArrayNC = Array("RAL 62", "RAL 63", "RAL 64", "SAMP-1", "SAMP-2", "2 1/2", "3 1/2", "HENRRICH", "BUNCHER", "REUNIDO")
ArrayCbox = Array("Cbox62", "Cbox63", "Cbox64", "CboxS1", "CboxS2", "CboxFO", "Cbox312", "CboxH", "CboxBnchr", "CboxReunido")
ArrayTxt = Array("Txt621", "Txt631", "Txt641", "TxtS11", "TxtS21", "TxtFO1", "Txt3121", "TxtH1", "TxtK11", "TxtReun1")
ArrayTArt = Array("Txt1Art62", "Txt1Art63", "Txt1Art64", "Txt1ArtS1", "Txt1ArtS2", "Txt1ArtFO", "Txt1Art312", "Txt1ArtH", "Txt1ArtK1", "Txt1ArtReun")
ArraySCbox = Array("Cbox61", "CboxMCu", "CboxMAl", "CboxA3", "CboxA4", "CboxA6", "CboxA7", "CboxA8", "CboxA9", "CboxA10", "CboxA11", "CboxA12", "CboxA13", "CboxA14", "CboxA15")
ArraySTxt = Array("Txt61", "TxtMCu1", "TxtMAl", "TxtA3", "TxtA4", "TxtA6", "TxtA7", "TxtA8", "TxtA9", "TxtA10", "TxtA11", "TxtA12", "TxtA13", "TxtA14", "TxtA15")
ArraySNC = Array("RAL 61", "MULTI-Cu", "MULTI-Al", "TRENZ. A3", "TRENZ. A4", "TRENZ. A6", "TRENZ. A7", "TRENZ. A8", "TRENZ. A9", "TRENZ. A10", "TRENZ. A11", "TRENZ. A12", "TRENZ. A13", "TRENZ. A14", "TRENZ. A15")
ArraySTArt = Array("TxtArtA3", "TxtArtA4", "TxtArtA6", "TxtArtA7", "TxtArtA8", "TxtArtA9", "TxtArtA10", "TxtArtA11", "TxtArtA12", "TxtArtA13", "TxtArtA14", "TxtArtA15")
'Comienza seleccionando la fecha
Fecha = UserForm3.CmbFecha
Sheets(3).Activate
Set rng = Cells.Find(What:=Fecha, after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Y = rng.Column

'Datos para Ral 62

For E = 0 To 9
Set Ptext = Me.Controls(ArrayTxt(E))
Set CCbox = Me.Controls(ArrayCbox(E))
Set Atext = Me.Controls(ArrayTArt(E))
Set MrcA = Me.Controls(ArrayMrcA(E))
Set MrcP = Me.Controls(ArrayMrcP(E))
MMaq = CStr(ArrayNC(E))
Select Case E
Case 0 To 6
Sheets(3).Activate
Case 7
Sheets(4).Activate
Case 8 To 9
Sheets(6).Activate
End Select
Select Case CCbox = ""

Case True
Respuesta = MsgBox("No Capturaste Operador en " & MMaq & "; ¿Es falta de programa?", vbYesNo)
If Respuesta = vbYes Then
CCbox = "F/P---OTRO OP. " & MMaq
Ptext = 0
Atext = "F/P"
Exit Sub
Else
Exit Sub
End If

Case False
Select Case Atext = ""
Case True
MsgBox ("No Capturaste el articulo del producto en " & MMaq)
Exit Sub
Case False
Select Case Ptext <> ""
Case True
Set rng = Cells.Find(What:=CCbox, after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
X = rng.Row
For Each TxtArt In MrcA.Controls
Cells(X, Y) = (TxtArt)
X = X + 1
Next TxtArt
X = rng.Row
For Each TxtProd In MrcP.Controls
If TxtProd = "" Then
Cells(X, Y).Offset(0, 1) = TxtProd
X = X + 1
Else
Cells(X, Y).Offset(0, 1) = CLng(TxtProd)
X = X + 1
End If
Next TxtProd

Case False
MsgBox "No Capturaste produccion en " & MMaq
Exit Sub
End Select
End Select
End Select
Next


'Datos para Ral 61
For E = 0 To 14
Select Case E
Case 0
Sheets(3).Activate
Case 1 To 2
Sheets(4).Activate
Case 3 To 14
Sheets(5).Activate
End Select
Set Ptext = Me.Controls(ArraySTxt(E))
Set CCbox = Me.Controls(ArraySCbox(E))
MMaq = CStr(ArraySNC(E))
Select Case CCbox = ""
Case True
Respuesta = MsgBox("No Capturaste Operador en " & MMaq & "; ¿Es falta de programa?", vbYesNo)
If Respuesta = vbYes Then
CCbox = "F/P---OTRO OP. " & MMaq
Ptext = 0
Exit Sub
Else
Exit Sub
End If
Case False

Select Case Ptext <> ""
Case True
Select Case E
Case E = 0 To 2
If Ptext.Name = "TxtMCu1" Then
Set rng = Cells.Find(What:=CCbox, after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
X = rng.Row
Cells(X, Y) = (CLng(TxtMCu1) + CLng(TxtMCu2) + CLng(TxtMCu3))
Else
Set rng = Cells.Find(What:=CCbox, after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
X = rng.Row
Cells(X, Y) = CLng(Ptext)
End If
Case E = 3 To 14
E = E - 3
Set Atext = Me.Controls(ArraySTArt(E))
Set rng = Cells.Find(What:=CCbox, after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
X = rng.Row
Cells(X, Y) = (Atext)
If Ptext = "" Then
Cells(X, Y).Offset(0, 1) = Ptext
Else
Cells(X, Y).Offset(0, 1) = CLng(Ptext)
End If
E = E + 3
Case False
MsgBox "No Capturaste produccion en " & MMaq
Exit Sub
End Select
End Select
End Select
Next
End Sub
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder