循环并将单元格值放入其中后,VBA Array为空 [英] VBA Array is empty after looping and putting cell values in it
问题描述
我有一个正在处理的宏,假设如果单元格的值等于IN,则循环遍历并将单元格的值存储到数组中.由于某种原因,数组为空.我是VBA的新手,并且怀疑我可能无法正确检索单元格值.下面是我的代码,感谢您的任何帮助,在此先感谢您.
I have a macro I'm working on and it's suppose to loop through and store a cell's value into the array if the cells value equals IN. For some reason the array is empty. I'm new to VBA and suspect I might not be retrieving the cells values properly. Below is my code any assistance is appreciated thanks in advance.
请注意,正在运行宏的Excel工作表实际上在那些单元格中包含内容,并且其中一些具有值IN.
Note the excel sheet the macro is being ran on does in fact have content in those cells and several with the value IN.
Option Explicit
'Variable Definitions ***********************************************************
Dim WorkbookSize As Long 'Var to hold number of rows in worksheet, used primarily to control loops
Dim newbook As Boolean 'Flag if new book was created correctly
Dim wb As Workbook 'var to hold workbook object
Dim TempPath As String 'var to hold local temp path to store newly created workbook
Dim i As Integer 'var used as counter for loops
Dim activeBook As String 'var to hold new workbook name
'Main Driver
Sub Main()
WorkbookSize = size() 'Run function to get workbook size
newbook = False
Call create 'Run sub to create new workbook
Call pull(WorkbookSize) 'Run sub to pull data
End Sub
'Get size of Worksheet
Function size() As Long
size = Cells(Rows.Count, "A").End(xlUp).Row
End Function
'Create workbook
Sub create()
Set wb = Workbooks.Add
TempPath = Environ("temp") 'Get Users local temp folder
With wb
.SaveAs Filename:=TempPath & "EDX.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
End With
End Sub
'pull data
Sub pull(size)
Dim code() As Variant
ReDim code(size - 1)
For i = 1 To size
'Check code column fo IN and Doctype column for 810
If Cells(i, 17).Value = "IN" Then
code(i) = Cells(i, 17).Value 'store in array
End If
Next i
Call push(code)
End Sub
'push data to new workbook
Sub push(ByRef code() As Variant)
activeBook = "TempEDX.xlsm"
Workbooks(activeBook).Activate 'set new workbook as active book
Dim txt As String
For i = 1 To UBound(code)
txt = txt & code(i) & vbCrLf
'Cells(i + 1, 1).Value = code(i)
Next i
MsgBox txt
End Sub
推荐答案
您应该完全限定对 Cells
属性的调用.否则, Cells
使用活动的工作簿和工作表.就您而言,您已经在扫描pull方法之前创建了一个工作簿.因此,您实质上是在看一个空的工作表.
You should fully qualify your calls to the Cells
Property. Otherwise Cells
uses the active workbook and worksheet. In your case, you've created a workbook before scanning in your pull method. So you're essentially looking at an empty worksheet.
在拉动之后创建新的工作簿,或者创建一个新的工作表变量并将其设置在开头,例如:
Either create the new workbook AFTER you pull, or create a new Worksheet variable and set it at the beginning like:
dim currentWorksheet as Worksheet
set currentWorksheet = Activesheet
然后,您应该将 currentWorksheet
传递到pull函数和size函数中.
Then, you should pass currentWorksheet
into the pull function and size function.
我会做这样的事情:
Option Explicit
'Variable Definitions ***********************************************************
Dim WorkbookSize As Long 'Var to hold number of rows in worksheet, used primarily to control loops
Dim newbook As Boolean 'Flag if new book was created correctly
Dim wb As Workbook 'var to hold workbook object
Dim TempPath As String 'var to hold local temp path to store newly created workbook
Dim i As Integer 'var used as counter for loops
Dim activeBook As String 'var to hold new workbook name
'Main Driver
Sub Main()
Dim currentWorksheet As Worksheet
Set currentWorksheet = ActiveSheet
WorkbookSize = size(currentWorksheet) 'Run function to get workbook size
newbook = False
Dim values()
values = pull(currentWorksheet, WorkbookSize) 'Run sub to pull data
push create(), values
End Sub
'Get size of Worksheet
Function size(sh As Worksheet) As Long
size = sh.Cells(Rows.COUNT, "A").End(xlUp).row
End Function
'Create workbook
Function create() As Workbook
Set wb = Workbooks.Add
TempPath = Environ("temp") 'Get Users local temp folder
With wb
.SaveAs Filename:=TempPath & "EDX.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
End With
Set create = wb
End Function
'pull data
Function pull(pullFromSheet As Worksheet, size) As Variant
Dim code() As Variant
ReDim code(size - 1)
For i = 1 To size
'Check code column fo IN and Doctype column for 810
If pullFromSheet.Cells(i, 17).Value = "IN" Then
code(i-1) = pullFromSheet.Cells(i, 17).Value 'store in array
End If
Next i
pull = code
End Function
'push data to new workbook
Sub push(toWorkbook As Workbook, ByRef code() As Variant)
'activeBook = "TempEDX.xlsm"
'Workbooks(activeBook).Activate 'set new workbook as active book
Dim newSheet As Worksheet
Set newSheet = toWorkbook.Sheets(1)
Dim txt As String
For i = 0 To UBound(code)
txt = txt & code(i) & vbCrLf
newSheet.Cells(i + 1, 1).Value = code(i)
Next i
MsgBox txt
newSheet.Activate 'just to make your new sheet active for the user
End Sub
我将Push代码移到了pull代码之外,并且还创建了函数而不是subs,因此您可以很好地处理要创建的新对象.
I moved the Push code outside of the pull code and also create functions instead of subs so you have good handles on the new objects you are creating.
这篇关于循环并将单元格值放入其中后,VBA Array为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!