我才刚刚开始涉足VBA,但遇到了一些障碍。
我有一个包含50多个列,900多个数据行的工作表。我需要重新格式化其中约10列,并将其粘贴在新的工作簿中。
如何以编程方式选择book1列中的每个非空白单元格,通过某些功能运行它,然后将结果放入book2中?
最佳答案
以下VBA代码应该可以帮助您入门。它将原始工作簿中的所有数据复制到新工作簿中,但是将为每个值加1,并且所有空白单元格都将被忽略。
Option Explicit
Public Sub exportDataToNewBook()
Dim rowIndex As Integer
Dim colIndex As Integer
Dim dataRange As Range
Dim thisBook As Workbook
Dim newBook As Workbook
Dim newRow As Integer
Dim temp
'// set your data range here
Set dataRange = Sheet1.Range("A1:B100")
'// create a new workbook
Set newBook = Excel.Workbooks.Add
'// loop through the data in book1, one column at a time
For colIndex = 1 To dataRange.Columns.Count
newRow = 0
For rowIndex = 1 To dataRange.Rows.Count
With dataRange.Cells(rowIndex, colIndex)
'// ignore empty cells
If .value <> "" Then
newRow = newRow + 1
temp = doSomethingWith(.value)
newBook.ActiveSheet.Cells(newRow, colIndex).value = temp
End If
End With
Next rowIndex
Next colIndex
End Sub
Private Function doSomethingWith(aValue)
'// This is where you would compute a different value
'// for use in the new workbook
'// In this example, I simply add one to it.
aValue = aValue + 1
doSomethingWith = aValue
End Function