VBA范围/数组问题。无法选择多个范围。 [英] VBA Range/Array Problem. Cannot choose multiple ranges.
问题描述
大家好，
编辑：它崩溃的部分是我更新的地方
< pre lang =vb> ar =范围（ C14：C19）' 购买
Var =范围（ D14：D19）' 价格
这是我选择数字范围的地方
' 不工作
' ar =范围（C& startRow，范围（C& endRow）.End（xlUp））'购买
' Var = Range（D& startRow，Range（D& endRow）.End（xlUp ））'价格
这是使用逐行计算的开始和结束范围选择范围，输出确实作为正确的开始和结束行。
我试图在代码中包含注释以使其可读，特别是因为我确信有更好的方法可以做到这一点！
我有一张特定产品的购买和销售表。例如
日期名称买入价卖出收盘成本加成FIFO PL
10/01/1900 AA0011 10 2 - 10 - 20 20
20/01/1900 AA0011 - 1 5 5 10 -10 -30
20/01/1900 AA0011 20 4 - 25 - 80 110
20/01/1900 AA0011 - 2 15 10 50 -50 -160
20/01/1900 AA0012 10 2 - 10 - 20 20
20/01/1900 AA0012 - 1 5 5 10 -10 -30
20 / 01/1900 AA0012 20 4 - 25 - 80 110
20/01/1900 AA0012 - 2 15 10 50 -50 -160
20/01/1900 AA0012 10 2 - 20 - 20 180
20/01/1900 AA0012 - 1 5 15 20 -20 -200
20/01/1900 AA0016 20 4 - 20 - 80 80
20/01/1900 AA0016 - 2 20 - 60 -60 -140
20/01/1900 AA0016 10 2 - 10 - 20 160
20/01/1900 AA0016 - 1 5 5 20 -20 -180
20/01/1900 AA0016 20 4 - 25 - 80 260
我正在尝试运行FIFO计算，计算出每个特定产品的销售量。计算总体上运行良好，但我很难让它专门为每个产品ID运行。
我尝试为每个产品选择基于查找的计算范围产品开始的地方，产品如下所示，然后选择该范围进行计算。
然而，当我在一个范围内使用我的startRow和endRow变量时（）函数，我只是让应用程序崩溃。
当我手动使用数字时，它完美地工作（虽然仅适用于我选择的产品）。
您对我的错误有什么建议吗？另外，我很感激任何提示如何改进我的代码！
谢谢。
< pre lang =vb> Sub RowCount（）
Dim sell As Long
Dim i As 整数
Dim j As 整数
Dim r As 整数
Dim cnt As Long
Dim sale As Long
Dim startRow 作为 整数
Dim endRow 作为 整数
Dim cStage 作为 整数
Dim pID 作为 新集合，ID
Dim productIDs（）作为 变体
Dim currProduct 作为 长
Dim ar 作为 变体
Dim Var As Variant
' CLEAR PREVIOUS
范围（ G10：G65536）。ClearContents
' 收集所有产品
productIDs（）=范围（ B10，范围（ B65536 ）。结束（xlUp））' ID
开启 错误 恢复 下一步
对于 每个 ID 在 productIDs
pID.Add ID，ID
下一步
' 每个计算销售额PRODUCT
对于 currProduct = 1 至 pID.Count
'
查找currProduct的开始和结束行
'
cStage = 0 ' 搜索第一行
' 计算产品的开始和结束行号
对于 r = 1 Rows.Count每行'
如果 pID（currProduct）=范围（ B& r）和 cStage = 0 然后 < span class =code-comment>' 找到当前产品ID的第一个
startRow = r ' 第一行是当前行
cStage = 1 转向搜索结束行
ElseIf pID（x）<>范围（ B& r）和 cStage = 1 然后 ' < span class =code-comment>找到第一行，现在传递最后一行
cStage = 2 ' 不再搜索
endRow = r - 1 < span class =code-comment>' 最终产品是前一行
结束 如果
下一步 r
' 工作（产品编号2）
ar =范围（ C14： C19）' 购买
Var =范围（ D14：D19）' 价格
' 不工作
' ar =范围（C& startRow，Range（C& endRow）.End（xlUp））'购买
' Var = Range（D& startRow，Range（D和endRow）.End（xlUp））'价格
'
' 对产品销售/每行进行计算
' 当currProduct的范围正确时工作
'
For i = 10 范围（ & Rows.Count）。结束（xlUp）。
< span class =code-keyword>如果 pID（currProduct）= Ran ge（ B& i）然后
sell =范围（ E& i）
sale = 0
j = 1
sell> 0 和 pID（currProduct）=范围（ B& i）
cnt = ar（j， 1 ）
ar（j， 1 ）= IIf（ar（j， 1 ）> sell，ar （j， 1 ） - sell， 0 ）' iif
sell = sell - （cnt - ar（j， 1 ））
sale = sale +（cnt - ar（j， 1 ））* Var（j， 1 ）
j = j + 1
循环
范围（ G1000）。结束（xlUp）（ 2 ）= sale ' 输出销售额
结束 如果
下一步我' 下次销售
下一步 currProduct ' 下一个产品
结束 Sub
好吧我花了一段时间，但我想我已经到了那里。
问题出在你的的计算中endRow和startRow
当单步执行代码时，我无法理解为什么r（循环计数器）不断重置为0.这是因为它被声明为整数但是你在循环中使用Rows.Count，这是一个Long。
所以第1步是使用Dim r 作为 长
Dim startRow 作为 长
Dim endRow 作为 长
但是，值仍未计算在这个阶段。问题出在比较中。所以第2步是将范围（B& r）更改为范围（B& r）.Value2这让我得到了
startRow但是endRow正在变为23而不是13.我追踪到了ElseIf PID（x）的。x未定义，因此默认为0.我认为应该是ElseIf pID（ currProduct ）...你可以通过使用代码模块顶部的以下行来避免这样的问题
Option Explicit这些值出现在工作表上，但需要很长一段时间！
而不是使用Rows.Count为什么不停在工作表中最后一个填充的行，例如Dim lastRow As Long
lastRow = Me .UsedRange.Rows（ Me .UsedRange.Rows。计数）。行
（注意 -我假设此VBA代码在正在使用的工作表上 - 如果你把这个代码放到一个基本模块中，你将不得不明确地识别这个工作表。）
此外，而不是以r开头= 1每次，初始化endRow = 9然后始终启动该循环来自
endRow + 1。我最终得到了这样的东西endRow = 9
' 计算每种产品的销售额
对于 currProduct = 1 pID.Count
'
' 找到currProduct的开始和结束行
'
Dim lastRow 作为 长
lastRow = 我 .UsedRange.Rows（ Me .UsedRange.Rows.Count）。
cStage = 0 ' s earching第一行
' 计算产品的开始和结束行号
对于 r = endRow + 1 To lastRow ' 每行
如果 pID（currProduct）=范围（ B& r）.Value2 和 cStage = 0 然后 ' 首先找到当前产品ID
startRow = r ' 第一行是当前行
cStage = 1 ' 转向搜索结束行
ElseIf pID（currProduct）<>范围（ B& r）.Value2 和 cStage = 1 然后 ' 找到第一行，现在通过最后一行
cStage = 2 不再搜索
endRow = r - 1 ' 最终产品是前一行
结束 如果
下一步 r我没有实际上继续调查下一位，但我怀疑你也可以让它更有效率。我会提到最后一个恐怖但
On Error Resume Next
不要这样做！
一般注意事项：在适当的上下文中使用代码。
例如，这两行代码可以返回不同的结果，具体取决于当前活动的工作表。
' ar =范围（C& startRow ，范围（C和endRow）.End（xlUp））'购买
' Var =范围（D& startRow，范围（D和endRow）.End（xlUp））'价格
所以，您必须将其替换为：
Dim rngPurchases As 范围，rngPrices 作为范围
' ...
设置 rngPurchases = ThisW orkbook.Worksheets（ Sheet1）。范围（ C& startRow& ：范围（ C& endRow）。 End （xlUp）.Address）
设置 rngPrices = ThisWorkbook.Worksheets（ Sheet1）。范围（ D& startRow& ：范围（ D & endRow）。结束（xlUp）.Address）
我建议返回基础知识 [ ^ ] 。
最后，强烈相信你可以使用数组公式实现这一点[ ^ ]。详情请见：
Excel 2010性能：优化性能障碍的提示 [ ^ ]
Hi all,
Edit: The part where it crashes is where I update
ar = Range("C14:C19") 'Purchases
Var = Range("D14:D19") 'Prices
which is where I select the range with the numbers to
'Not Working
'ar = Range("C" & startRow, Range("C" & endRow).End(xlUp)) 'Purchases
'Var = Range("D" & startRow, Range("D" & endRow).End(xlUp)) 'Prices
which is where the range is selected using the start and end range calculated using the row by row, which the output does come out as the correct start and end rows.
I've tried to include comments with the code to make it readable, especially as I'm sure there are better ways of doing this!
I have a table with purchases and sales for a specific product. E.g.
Date Name Buy Price Sell Close Sold Cost Addition FIFO PL
10/01/1900 AA0011 10 2 - 10 - 20 20
20/01/1900 AA0011 - 1 5 5 10 -10 -30
20/01/1900 AA0011 20 4 - 25 - 80 110
20/01/1900 AA0011 - 2 15 10 50 -50 -160
20/01/1900 AA0012 10 2 - 10 - 20 20
20/01/1900 AA0012 - 1 5 5 10 -10 -30
20/01/1900 AA0012 20 4 - 25 - 80 110
20/01/1900 AA0012 - 2 15 10 50 -50 -160
20/01/1900 AA0012 10 2 - 20 - 20 180
20/01/1900 AA0012 - 1 5 15 20 -20 -200
20/01/1900 AA0016 20 4 - 20 - 80 80
20/01/1900 AA0016 - 2 20 - 60 -60 -140
20/01/1900 AA0016 10 2 - 10 - 20 160
20/01/1900 AA0016 - 1 5 5 20 -20 -180
20/01/1900 AA0016 20 4 - 25 - 80 260
I am trying to run a FIFO calculation whereby it calculates how many are sold per specific product. The calculation works fine overall, but I'm having difficulty making it run specifically for each product ID.
I tried to for each product select the calculated range based on finding where the product starts and the product ends as below, then select that range to do the calculation on.
However, when I use my startRow and endRow variables in a Range() function, I just get the application crashing.
When I use the numbers by hand, it works perfectly (although only for the product I've selected).
Do you have any advice as to what I am doing wrong with this? Also I'd be grateful for any tips how to improve my code!
Thanks.
Sub RowCount()
Dim sell As Long
Dim i As Integer
Dim j As Integer
Dim r As Integer
Dim cnt As Long
Dim sale As Long
Dim startRow As Integer
Dim endRow As Integer
Dim cStage As Integer
Dim pID As New Collection, ID
Dim productIDs() As Variant
Dim currProduct As Long
Dim ar As Variant
Dim Var As Variant
'CLEAR PREVIOUS
Range("G10:G65536").ClearContents
'COLLECT ALL PRODUCTS
productIDs() = Range("B10", Range("B65536").End(xlUp)) 'IDs
On Error Resume Next
For Each ID In productIDs
pID.Add ID, ID
Next
'CALCULATE SALES FOR EACH PRODUCT
For currProduct = 1 To pID.Count
'
' FIND START AND END ROW FOR currProduct
'
cStage = 0 'searching for the first row
'calculate start and end row numbers for product
For r = 1 To Rows.Count 'for each row
If pID(currProduct) = Range("B" & r) And cStage = 0 Then 'found first of current product ID
startRow = r 'first row is current row
cStage = 1 ' moving to searching for the end row
ElseIf pID(x) <> Range("B" & r) And cStage = 1 Then 'found the first row and now passed the final row
cStage = 2 'search no more
endRow = r - 1 'final product was previous row
End If
Next r
'Working (for product number 2)
ar = Range("C14:C19") 'Purchases
Var = Range("D14:D19") 'Prices
'Not Working
'ar = Range("C" & startRow, Range("C" & endRow).End(xlUp)) 'Purchases
'Var = Range("D" & startRow, Range("D" & endRow).End(xlUp)) 'Prices
'
' PERFORM CALCULATIONS ON PRODUCT SALES/EACH ROW
' WORKS WHEN currProduct's RANGE IS CORRECT
'
For i = 10 To Range("A" & Rows.Count).End(xlUp).Row
If pID(currProduct) = Range("B" & i) Then
sell = Range("E" & i)
sale = 0
j = 1
Do While sell > 0 And pID(currProduct) = Range("B" & i)
cnt = ar(j, 1)
ar(j, 1) = IIf(ar(j, 1) > sell, ar(j, 1) - sell, 0) 'iif
sell = sell - (cnt - ar(j, 1))
sale = sale + (cnt - ar(j, 1)) * Var(j, 1)
j = j + 1
Loop
Range("G1000").End(xlUp)(2) = sale 'output the sales
End If
Next i 'next sale
Next currProduct 'next product
End SubWell it took me a while but I think I've got there.
The problem is in your calculation ofendRowandstartRow
When stepping through the code I couldn't understand whyr(the loop counter) kept getting reset to 0. It is because it is declared as an integer but you are usingRows.Countin your loop which is aLong.
So step 1 is to useDim r As Long Dim startRow As Long Dim endRow As Long
However, the values are still not being calculated at this stage. Problem was in the comparison. So step 2 is to changeRange("B" & r)to beRange("B" & r).Value2That got me as far as getting the
startRowbut endRow was coming up as 23 instead of 13. I tracked that one down toElseIf pID(x).xis undefined and so is defaulting to 0. I believe that should beElseIf pID(currProduct)...You can avoid problems like that by using the following line at the top of your code module
Option ExplicitThat got values appearing on the sheet, but it takes a long time!
Instead of usingRows.Countwhy not stop at the last populated row in the sheet e.g.Dim lastRow As Long lastRow = Me.UsedRange.Rows(Me.UsedRange.Rows.Count).Row
(Note - theMeassumes that this VBA code is on the Sheet that is being used - if you put this code into a basic module you will have to explicitly identify the sheet instead).
Also, instead of starting withr=1each time, initialiseendRow = 9then always start that loop from
endRow + 1. I ended up with something like thisendRow = 9 'CALCULATE SALES FOR EACH PRODUCT For currProduct = 1 To pID.Count ' ' FIND START AND END ROW FOR currProduct ' Dim lastRow As Long lastRow = Me.UsedRange.Rows(Me.UsedRange.Rows.Count).Row cStage = 0 'searching for the first row 'calculate start and end row numbers for product For r = endRow + 1 To lastRow 'for each row If pID(currProduct) = Range("B" & r).Value2 And cStage = 0 Then 'found first of current product ID startRow = r 'first row is current row cStage = 1 ' moving to searching for the end row ElseIf pID(currProduct) <> Range("B" & r).Value2 And cStage = 1 Then 'found the first row and now passed the final row cStage = 2 'search no more endRow = r - 1 'final product was previous row End If Next rI didn't actually carry on looking into the next bit, but I suspect you can make it more efficient too. I will mention one last horror though
On Error Resume Next
Don't do it!
One general note: Use code in proper context.
For example, these two lines of code can return different results depending on what sheet is currently active.
'ar = Range("C" & startRow, Range("C" & endRow).End(xlUp)) 'Purchases 'Var = Range("D" & startRow, Range("D" & endRow).End(xlUp)) 'Prices
So, you have to replace it with:
Dim rngPurchases As Range, rngPrices As Range '... Set rngPurchases = ThisWorkbook.Worksheets("Sheet1").Range("C" & startRow & ":" Range("C" & endRow).End(xlUp).Address) Set rngPrices = ThisWorkbook.Worksheets("Sheet1").Range("D" & startRow & ":" Range("D" & endRow).End(xlUp).Address)
I do recommend to return to basics[^].
Finally, strongly do believe that you can achieve that using array formula[^]. For further information, please see:
Excel 2010 Performance: Tips for Optimizing Performance Obstructions[^]
这篇关于VBA范围/数组问题。无法选择多个范围。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
