有沒有辦法在不同行的不同列數上運行這個宏？-有解無憂

Sub pipey()
'Create a text file with each row cell delimited with "|"

Dim intUsedrows As Integer
Dim intUsedcolumns As Integer

Open "Y:\purchasing\Pipey.txt" For Output As #1

With Worksheets(1).Range("a:f")
intUsedrows = ActiveSheet.UsedRange.Rows.Count
intUsedcolumns = ActiveSheet.UsedRange.Columns.Count

For i = 1 To intUsedrows
  For j = 1 To intUsedcolumns - 1
   Print #1, .Cells(i, j); "|";
  Next j
   Print #1, .Cells(i, intUsedcolumns)
  Next i
End With
Close #1
MsgBox ("Done Successfully")
End Sub

第一行始終為 12 列，最后一行始終為 3 列，中間的行始終為 9 列。我試圖避免在生成的文本檔案中的行末尾使用尾隨分隔符。

uj5u.com熱心網友回復：

嘗試先將輸出放入字串，而不是直接放入文本檔案。然后在將整個字串輸出到文本檔案之前運行一個回圈以消除尾隨列。
例如：

Do Until Not strOutput Like "*||*"
    strOutput = Replace(strOutput, "||", "|")
Loop

uj5u.com熱心網友回復：

剛剛意識到有一個更簡單的答案；如果沒有要保留的空白單元格（即給定范圍內的所有相關單元格中至少有一些內容），則將For 回圈更改為：

For i = 1 To intUsedrows
    j = 1
    Do
        If .Cells(i, j 1) <> "" Then
            Print #1, .Cells(i, j); "|";
            j=j 1
        Else
            Print #1. .Cells(i, j)
            Exit Do
        End If
    Loop
Next i

uj5u.com熱心網友回復：

如果我有應用它的技能，我相信 Spences 解決方案會解決它。當我嘗試使用它時，我們的某人提出了這個可行的建議。

Sub pipey()
  'Create a text file with each row cell delimited with "|"
  Dim i As Long, j As Long, lr As Long, lc As Long
  
  Open "Y:\purchasing\Pipey.txt" For Output As #1

  With Sheets(1)
    lr = .Range("A" & Rows.Count).End(3).Row
    
    'first row is always 12 columns
    For j = 1 To 12
      Print #1, .Cells(1, j); IIf(j = 12, "", "|");
    Next
    Print #1,
    
    'the rows in between are always 9 columns
    For i = 2 To lr - 1
      For j = 1 To 9
        Print #1, .Cells(i, j); IIf(j = 9, "", "|");
      Next
      Print #1,
    Next
    
    'the last row is always 3 columns
    For j = 1 To 3
      Print #1, .Cells(lr, j); IIf(j = 3, "", "|");
    Next
    Print #1,
  End With
  
  Close #1
  MsgBox ("Done Successfully")
End Sub

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/492098.html

標籤：擅长 vba

上一篇：添加到字典時VBA錯誤457鍵

下一篇：命名范圍指的是輸入與輸出不同