excel - 范围变量不接受范围值
问题描述
我在这个项目上零星地工作了大约六个月。我终于遇到了一个我无法修复的错误。代码如下。MaxIFS() 从此链接上的代码修改。我还附上了两个屏幕截图,显示了错误消息和引发错误的代码行。我为代码的粗略状态道歉。
Option Explicit
Sub CountSeats()
Dim lNoSeats, lG2, lastrow, lStateRow, lStateSeats, lStateNo As Long
Dim sFileName, sPathName, sFunction, sStateAbbr As String
Dim wsSource, wsTarget As Worksheet
Dim rMaxRange, rLookup1 As Range
Dim vVar_Range1 As Variant
Set wsSource = ThisWorkbook.Worksheets("Priority Values calculated")
Dim wbSource, wbTarget As Workbooks
lNoSeats = wsSource.Range("G2").Value
'Gotta get the slash going in the right direction for Mac/Windows
#If Mac Then
sPathName = ThisWorkbook.Path & " / "
#Else
sPathName = ThisWorkbook.Path & "\"
#End If
wsSource.Copy
sFileName = sPathName & lNoSeats & " seats for apportionment.xlsm"
If Len(Dir(sFileName)) > 0 Then
' First remove readonly attribute, if set
SetAttr sFileName, vbNormal
' Then delete the file
Kill sFileName
End If
Set wsTarget = ThisWorkbook.Worksheets("Priority Values calculated")
'ActiveWorkbook.SaveAs FileName:=sPathName & lNoSeats & " seats for apportionment.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
lNoSeats = wsTarget.Range("G2").Value
'Copy and paste G2 to replace formula with value
wsTarget.Range("G2").Copy
wsTarget.Range("G2").PasteSpecial (xlPasteValues)
lastrow = wsTarget.Cells(Rows.Count, 6).End(xlUp).Row
'ActiveWorkbook.Save
With wsTarget
rMaxRange = "E2:E" & lastrow
rLookup1 = "C2:C" & lastrow
End With
For lStateNo = 2 To 51
'sStateAbbr = wsTarget.Range("C" & lStateNo)
sStateAbbr = "CA"
lStateSeats = MaxIF((rMaxRange), (rLookup1), sStateAbbr)
wsTarget.Range("H" & lastrow) = lStateSeats
Next lStateNo
End Sub
Function MaxIF(rMaxRange As Range, rLookup1 As Range, vVar_Range1 As Variant) As Variant
Dim vLU1 As Variant
Dim lfounds As Long
Dim rcell As Range
vLU1 = rLookup1.Value2 '<--| store Lookup_Range1 values
ReDim lValuesForMax(1 To rMaxRange.Rows.Count) As Long '<--| initialize lValuesForMax to its maximum possible size
For Each rcell In rMaxRange.Columns(1).SpecialCells(xlCellTypeConstants, xlNumbers)
If vLU1(rcell.Row, 1) = vVar_Range1 Then '<--| check 'rLookup1' value in corresponding row of current 'MaxRange' cell
lfounds = lfounds + 1
lValuesForMax(lfounds) = CLng(rcell) '<--| store current 'rMaxRange' cell
End If
Next rcell
ReDim Preserve lValuesForMax(1 To lfounds) '<--| resize ValuesForMax to its actual values number
MaxIF = Application.Max(lValuesForMax)
End Function
解决方案
尝试这个 :
Set rMaxRange = Range("E2:E" & lastrow)
Set rLookup1 = Range("C2:C" & lastrow)
问候,
推荐阅读
- java - 在 Eclipse SWT 指针中覆盖 performSave()/doSave() 方法
- node.js - 如何将多个数据类型分配给mongodb中的变量?
- java - 动画矢量可绘制动画无法正常工作
- azure - 如何帮助 LUIS 检测“半英里”、“四分之一磅”等
- c# - 访问路径'/proc/1/map_files'被拒绝.NET Core 2.1 Centos 7
- frameworks - 示例 Appium 框架
- d3.js - D3 图表集成到 Vuejs
- c# - 使用带有字符串拆分的 linq
- mysql - 在单个 UPDATE 请求中更新多条记录
- node.js - 每个 POST 请求的 HTTP 抓取速度更快?