类型不匹配 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11246717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Type mismatch VBA
提问by Viktor Mellgren
This works Lastrow = 8, but not 9 (Type mismatch)
这适用于 Lastrow = 8，但不适用于 9（类型不匹配）
If i remove If Not (myarray = Empty) Thenit does not work for 8
如果我删除If Not (myarray = Empty) Then它不起作用 8
What is the easiest way to solve this?
解决这个问题的最简单方法是什么？
Public Function GetRowToWriteOn(ByVal SheetName As String, ByVal idnr As Integer) As Integer
LastRow = (Sheets(SheetName).UsedRange.Rows.Count) + 1
MsgBox (LastRow)
myarray = Sheets(SheetName).Range("d8:d" & LastRow).Value
If Not (myarray = Empty) Then
For row = 1 To UBound(myarray, 1)
If (myarray(row, 1) = idnr) Then
GetRowToWriteOn = row
Exit Function
End If
Next
End If
GetRowToWriteOn = LastRow
Exit Function
End Function
回答by SeanC
MyArray is taking 2 different types, depending on the range given.
If you are looking at 1 cell, then it is a single variant (which can be tested if it is Empty)
If you are looking at 2 or more cells, then it becomes an array of variant, so you would have to test each cell.
MyArray 采用 2 种不同的类型，具体取决于给定的范围。
如果您查看 1 个单元格，则它是单个变体（如果它为空，则可以进行测试）
如果您查看 2 个或更多单元格，则它成为变体数组，因此您必须测试每个单元格.
myarray = Sheets(SheetName).Range("d8:d8").Value- myarray gets the value in d8myarray = Sheets(SheetName).Range("d8:d9").Value- myarray(1,1) gets the value in d8, and myarray(2,1) gets the value in d9
myarray = Sheets(SheetName).Range("d8:d8").Value- myarray 获取 d8 中的值myarray = Sheets(SheetName).Range("d8:d9").Value- myarray(1,1) 获取 d8 中的值，而 myarray(2,1) 获取 d9 中的值
to test, use:
要测试，请使用：
if vartype(myarray)=vbArray then
' run through the array
else
' do single value stuff
endif
回答by Brad
I feel like your code should look more like this
我觉得你的代码应该更像这样
Option Explicit
Public Function GetRowToWriteOn(ByVal SheetName As String, ByVal idnr As Integer) As Integer
Dim lastrow As Long, row As Long
lastrow = (Sheets(SheetName).UsedRange.Rows.Count) + 1
MsgBox (lastrow)
Dim myarray() As Variant
myarray = Sheets(SheetName).Range("d8:d" & lastrow).Value
If Not (IsEmpty(myarray)) Then
For row = 1 To UBound(myarray, 1)
If (myarray(row, 1) = idnr) Then
GetRowToWriteOn = row
Exit Function
End If
Next
End If
GetRowToWriteOn = lastrow
Exit Function
End Function
BUT I also think there is another way to do what you want. A little simpler and used built in functions. I think I captured your intention here:
但我也认为还有另一种方法可以做你想做的事。稍微简单一点，使用内置函数。我想我在这里抓住了你的意图：
Dim RowToWriteOn As Long, SheetName As String, lastRow As Long
Dim rng As Range
SheetName = "Sheet1"
lastRow = (Sheets(SheetName).UsedRange.Rows.Count) + 1
Set rng = Sheets(SheetName).Range("d" & lastRow)
RowToWriteOn = rng.End(xlUp).row
回答by Tim Williams
Public Function GetRowToWriteOn(ByVal SheetName As String, _
ByVal idnr As Integer) As Long
Dim lastRow As Long, f As Range
lastRow = Sheets(SheetName).Cells(Rows.Count, 4).End(xlUp).Row
Set f = Sheets(SheetName).Range("D8:D" & lastRow).Find(what:=idnr, _
lookat:=xlWhole)
If Not f Is Nothing Then
GetRowToWriteOn = f.Row
Else
GetRowToWriteOn = lastRow + 1
End If
End Function
回答by Trace
myarray = Sheets(SheetName).Range("d8:d" & LastRow)
(without value)...
And you can use: if ubound(myArray) > 1 then ;..
（没有价值）......你可以使用： if ubound(myArray) > 1 then ;..
I think it could be as easy as this, no...?
我想就这么简单，不是吗……？
