I've made an excel sheet intended to be shared but other users are having problems to use it. When i use it there are no errors. But people say that they are getting error 438. I've asked them to send a screenshot of the debugging and this is what one of them sent
The object that's refreshing is a table that extracts information from a .txt file
My first suspect was that the user was inputing incorrect information on the .txt file and because of that the table wasn't recognizing it's format. However that user claims to be inputing the correct information. Also he is not the only one with that problem and it seems unlikely that 2 people could be inserting the wrong information when it's just copy and paste on the .txt file
Seeing the interface he has on the screenshot i belive hw is using excel 2010. I'm using 2013. Could the error because by an incompatibility on that specific line? Here's the full code
Sub Regisr()
'status bar
Application.DisplayStatusBar = True
Application.StatusBar = "0%..."
'desproteger y actualizar
ActiveSheet.Unprotect
Range("a3").Select
Selection.ListObject.TableObject.Refresh
'defincion de variables
'contar referidos viejos
Dim ok As Integer
Dim Nook As Integer
Nook = Application.WorksheetFunction.CountIf(Range("k:k"), "=NO OK")
ok = Application.WorksheetFunction.CountIf(Range("k:k"), "=Ok") + 2
'contar referidos nuevos
Dim Nook2 As Integer
Nook2 = Application.WorksheetFunction.CountIf(Range("i:i"), "=NO OK")
Application.Calculate
'ordenar azul por no ok
Range("a3", Range("i3").End(xlDown)).Sort key1:=Range("i3"), order1:=xlDescending, key2:=Range("a3"), Header:=xlYes
Application.Calculate
'ordenar naranja por no ok
Range(Range("k3").End(xlToRight), Range("k3").End(xlDown)).Sort key1:=Range("k3"), order1:=xlDescending, key2:=Range("o3"), Header:=xlYes
Application.Calculate
'borrar referidos viejos
Dim nook3 As Integer
If Nook = 0 Then
nook3 = 1
Else
nook3 = Nook
End If
Range(Range("l2").End(xlToRight).Offset(ok - 1, 0), Range("l" & nook3).Offset(ok, 0)).ClearContents
Application.Calculate
'agrandar tabla naranja
If Nook2 > Nook Then
Dim fondo As String
Dim derecha As String
fondo = Range("a2").End(xlDown).Offset(0, 11).Address
derecha = Range("k2").End(xlToRight).Address
ActiveSheet.ListObjects("Tabla26").Resize Range("k2", Range(derecha, fondo))
Range(Range("k3").End(xlToRight), Range("k3").End(xlDown)).Sort key1:=Range("k3"), order1:=xlDescending, key2:=Range("n3"), Header:=xlYes
End If
'ordenar naranja por no ok
Range(Range("k3").End(xlToRight), Range("k3").End(xlDown)).Sort key1:=Range("k3"), order1:=xlDescending, key2:=Range("o3"), Header:=xlYes
Application.Calculate
'status bar
Application.StatusBar = "25%..."
'copiar referidos nuevos
Dim nook4 As Integer
If Nook2 = 0 Then
nook4 = 1
Else
nook4 = Nook2
End If
Range(Range("a2").Offset(ok - 1, 0), Range("a" & nook4).Offset(ok, 0)).Copy
Range(Range("o1").Offset(ok, 0), Range("o1").Offset(ok + nook4 - 1, 0)).PasteSpecial (xlPasteValues)
Application.Calculate
Application.StatusBar = "50%..."
'copiar y pegar clicks de ayer
Range("e3", Range("e3").End(xlDown)).Copy
Range("n2").End(xlToRight).Offset(1, 1).PasteSpecial (xlPasteValues)
'Copiar y pegar clicks de hoy
Range("d3", Range("d3").End(xlDown)).Copy
Range("n3").PasteSpecial (xlPasteValues)
Application.Calculate
'status bar
Application.StatusBar = "75%..."
'reduccion de referidos
If Nook > Nook2 Then
Range(Range("k2").Offset(ok - 1, 0), Range("k" & nook3).Offset(ok, 0)).EntireRow.Delete
End If
'Asegurar columnas ocultas
'actualizador del resumen
resuregis = Sheets(6).Name
Range(Range("l3"), Range("o3").End(xlDown).Offset(0, -3)).Formula = "=if(isnumber(match([@Columna1],'" & resuregis & "'!$A:$A,0)),1,0)"
'contador de porcentaje
datos = Sheets(2).Name
Range(Range("m3"), Range("o3").End(xlDown).Offset(0, -2)).Formula = "=if(isnumber(index($1:$1048576,row(),match(" & datos & "!$G$74-1,$1:$1,0))),sum(index($1:$1048576,row(),match(" & datos & "!$G$74-1,$1:$1,0)),-index($1:$1048576,row(),match(" & datos & "!$G$74-2,$1:$1,0))))"
'crear nuevas fechas
Range("q1:s1").Copy
Range("k2").End(xlToRight).Offset(-1, 1).PasteSpecial xlPasteAll
Application.CutCopyMode = False
'ocultar columnas
Dim cantcolum As Integer
cantcolum = Application.WorksheetFunction.Count(Range("p1", Range("p1").End(xlToRight))) - 25 + 15
Range(Cells(1, 16), Cells(1, cantcolum)).EntireColumn.Hidden = True
'volver al inicio
Range("a1").Select
'proteger
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'status bar
Application.StatusBar = "100%..."
End Sub
Aucun commentaire:
Enregistrer un commentaire