Intentando llenar algunas celdas de un archivo TSV. No puedo encontrar ninguna razón por la que esto no funcione. Se establece "Interrumpir en todos los errores", no arroja errores. La matriz está perfectamente poblada desde el flujo de texto. Las cláusulas de protección funcionan según lo previsto. El incremento de la variable de desplazamiento de columna y la dirección resultante son correctos. Las celdas no están bloqueadas y la protección de hojas no está habilitada de todos modos. Y funciona bien para el primer conjunto de números/texto/fecha. Pero para los 22 conjuntos restantes no hace nada. Probé los dispositivos .Range("A1") y .MergedArea después de .Offset(...) . Office365 Pro. Estoy perplejo.
Sub chartTextData(ByVal pathToData As String, dateEarliest As Date, Optional ByVal strFile As String) On Error GoTo LoopExit Debug.Print Now() & " chartTextData BEGIN" Application.EnableEvents = False Application.Calculation = xlCalculationManual Dim fso As FileSystemObject Set fso = New FileSystemObject Dim TxtStream As textStream Dim linebuffer Dim myArray Dim cellAnchor As Range Dim i As Long Debug.Assert i = 0 Set cellAnchor = ActiveSheet.Range("D39") '38 on some Set TxtStream = fso.OpenTextFile(pathToData & strFile, ForReading, False, TristateUseDefault) Do While Not TxtStream.AtEndOfStream linebuffer = TxtStream.ReadLine ' 0 1 2 3 4 5 6 7 '33.19,$F$38,good,No Need to Act,11/20/2014,2100,DB,2 myArray = Split(linebuffer, vbTab, , vbTextCompare) 'header row, skip it If myArray(1) Like "*DATE*" Then GoTo JumpHereToBypassOlderThanDateEarliest If myArray(1) < dateEarliest Then GoTo JumpHereToBypassOlderThanDateEarliest 'Set cellAnchor = ActiveSheet.Range(myArray(1)) 'eg, "$D$39" cellAnchor.Offset(0, i).Value2 = CDbl(myArray(0)) 'test value cellAnchor.Offset(3, i).Value2 = CDate(myArray(1)) 'Date cellAnchor.Offset(2, i).Value2 = myArray(2) 'time cellAnchor.Offset(4, i).Value2 = myArray(3) 'Tech 'Debug.Print myArray(0) i = i + 2 'merged cells, 2 per 'Debug.Print i & " <--i" If i >= 46 Then GoTo LoopExit JumpHereToBypassOlderThanDateEarliest: Loop LoopExit: TxtStream.Close Set TxtStream = Nothing Set fso = Nothing Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.Calculate Debug.Print Now() & " chartTextData END" End SubEsto funciona , y no sé por qué. No usar i para el desplazamiento de columna, activar la celda superior de cada rango vertical para que se complete y volver a establecer cellAnchor en ella.
Sub chartTextData(ByVal pathToData As String, dateEarliest As Date, Optional ByVal strFile As String) On Error GoTo LoopExit Debug.Print Now() & " chartTextData BEGIN" Application.EnableEvents = False Application.Calculation = xlCalculationManual Dim fso As FileSystemObject Set fso = New FileSystemObject Dim TxtStream As textStream Dim linebuffer Dim myArray Dim cellAnchor As Range Dim i As Long Set cellAnchor = ActiveSheet.Range("D39") '38 on some cellAnchor.Activate Set TxtStream = fso.OpenTextFile(pathToData & strFile, ForReading, False, TristateUseDefault) Do While Not TxtStream.AtEndOfStream linebuffer = TxtStream.ReadLine ' 0 1 2 3 4 5 6 7 '33.19,$F$38,good,No Need to Act,11/20/2014,2100,DB,2 myArray = Split(linebuffer, vbTab, , vbTextCompare) 'header row, skip it If myArray(1) Like "*DATE*" Then GoTo JumpHereToBypassOlderThanDateEarliest If myArray(1) < dateEarliest Then GoTo JumpHereToBypassOlderThanDateEarliest cellAnchor.Offset(0, 0).Value2 = CDbl(myArray(0)) 'test value cellAnchor.Offset(3, 0).Value2 = CDate(myArray(1)) 'Date cellAnchor.Offset(2, 0).Value2 = myArray(2) 'time cellAnchor.Offset(4, 0).Value2 = myArray(3) 'Tech 'Debug.Print myArray(0) i = i + 2 'merged cells, 2 per cellAnchor.Offset(0, 2).Activate Set cellAnchor = ActiveCell If i >= 46 Then GoTo LoopExit JumpHereToBypassOlderThanDateEarliest: Loop LoopExit: TxtStream.Close Set TxtStream = Nothing Set fso = Nothing Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.Calculate Debug.Print Now() & " chartTextData END" End Sub ```**