我有以下代碼，當指向特定檔案夾時，它將在我的檢查選項卡中捕獲以下資料：檔案名、行數、列數。我需要幫助的最后一部分是找到一個標題，說它的“值”，然后對列求和，從單元格 d8 開始發布與每個檔案名相鄰的總數。代碼如下。任何想法如何輕松做到這一點？
Sub CollectData()
Dim fso As Object, xlFile As Object
Dim sFolder$
Dim r&, j&, k&
'*
Sheets("Check").Activate
Range("F8:I50").ClearContents
Range("A8:D50").Copy Range("F8")
Range("A8:D50").ClearContents
'*
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.path
If .Show Then sFolder = .SelectedItems(1) Else Exit Sub
End With
Set fso = CreateObject("Scripting.FileSystemObject")
For Each xlFile In fso.GetFolder(sFolder).Files
With Workbooks.Open(xlFile.path, Password:="password")
With .Sheets(1)
j = .Cells(.Rows.Count, 1).End(xlUp).Row
k = .Cells(1, Sheet1.Columns.Count).End(xlToLeft).Column
End With
.Close False
End With
r = r 1
Cells(r 7, 1).Value = xlFile.Name
Cells(r 7, 2).Value = j
Cells(r 7, 3).Value = k
ActiveWorkbook.Save
Next
End Sub
uj5u.com熱心網友回復：
我只會遍歷標題單元格并檢查 cell.value：
Dim headers As Range
Dim c As Range
Dim SumRange As Range
Dim Sum As Double
Set headers = Range("F8:I8")
For Each c In headers
If c.Value = "value" Then
'From the header, go 1 cell down and get range of continous non blank cells
'Set the SumRange variable to this range of cells
Set SumRange = Range(c.Offset(1, 0), c.End(xlDown))
End If
Next
'Iterate over the SumRange cells, and add to Sum variable as you go
For Each c In SumRange
Sum = Sum c.Value
Next
'Display Sum in destination cell
Cells(r 7, 4).Value = Sum
干杯!
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/465701.html