用数据填充范围

Question

我正在处理几个包含数据的Ranges，在这些Ranges中，我必须计算平均值和其他东西。但在我这样做之前，我需要找到异常值并将其标记为异常值。大多数都很好，但我在尝试填充Range时遇到了一个问题，我想将异常值标记为异常值。我以前读过，除了用值调用函数的Cell之外，我什么都不能填充。这就是为什么我尝试了潜水艇。

请看下面我想要实现的简单版本。

我收到的错误是数据类型有问题

Function MySheet()
'returns a String containing the Worksheet Name
MySheet = Application.Caller.Worksheet.Name
End Function
Sub FillIt(Register As String, x As Long, y As Long, Val As String)
Debug.Print Register, x, y, Val, TypeName(Val)
' I checked the passed Values which have made it until here and they seem fine
Sheets(Register).Cells(x, y) = Val ' The problem must lie here but I have almost the same in another sub and there it works.
End Sub
Function Fill(myRange As Range) ' This is what will get called from the worksheet
Dim Register As String
Dim i As Long, j As Long
Register = MySheet()
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
Debug.Print i, j
If i * j Mod 2 = 0 Then
Call FillIt(Register, myRange.Row + i - 1, myRange.Column + j - 1, "o") ' there is an issue with this line
End If
Next j
Next i
Fill = 88 ' just as a return value to check if it works
End Function

Answer 1

根据定义，函数返回一个值-它们不允许影响其他单元格。为了像这样影响多个细胞，你需要在Sub中进行。我很快将你的应用程序调整到下面进行测试，它似乎很有效("工作"=没有错误地完成(，但显然你需要一些调用sub的方法。

Sub FillIt(Register As String, x As Long, y As Long, Val As String)
Debug.Print Register, x, y, Val, TypeName(Val)
' I checked the passed Values which have made it until here and they seem fine
Sheets(Register).Cells(x, y) = Val ' The problem must lie here but I have almost the same in another sub and there it works.
End Sub
Sub Fill(myRange As Range) ' This is what will get called from the worksheet
Dim Register As String
Dim i As Long, j As Long
Register = ActiveSheet.Name
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
Debug.Print i, j
If i * j Mod 2 = 0 Then
Call FillIt(Register, myRange.Row + i - 1, myRange.Column + j - 1, "o") ' there is an issue with this line
End If
Next j
Next i

End Sub
Sub Testing()
Call Fill(ActiveSheet.Range("C10:E15"))
End Sub

如果你想在各种不同的范围内这样做，我建议在子的开头放一个Application.Inputbox来设置你的范围变量。