excel - 如何将单元格的文本内容分配给 Excel VBA 中的变量？

Question

我收到运行时错误“13”:Set tempChar = Range(Cells(iSourceRow, 31), Cells(iSourceRow, 31)) 上的类型不匹配以下代码中的行:

Sub GetStratGoalResponses()
'
' GetStratGoalResponses Macro
'
' Keyboard Shortcut: Ctrl g
'
    Dim iSourceRow As Integer
    Dim iTargetRow As Integer
    Dim tempChar As Characters

    iTargetRow = 1

    For iSourceRow = 2 To 28
        Worksheets("Survey_Responses_Oct_12,_2015").Activate
        If Range(Cells(iSourceRow, 31), Cells(iSourceRow, 31)) = "" Then End
        Set tempChar = Range(Cells(iSourceRow, 31), Cells(iSourceRow, 31))
MsgBox "About to process Strategic Goal Response ", tempChar
        Range(Cells(iSourceRow, 31), Cells(iSourceRow, 31)).Select
        Selection.Copy
        Worksheets("Strategic Goal Parsed").Activate
        Range("A2").Select
        ActiveSheet.Paste
        Set tempChar = Range("A2")
MsgBox "Just pasted response of ", tempChar
        Range("A4:A9").Select
        Selection.Copy
        Range(Cells(iTargetRow, 53), Cells(iTargetRow, 53)).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A2:A2").Select
        Selection.Clear
        iTargetRow = iTargetRow + 6
    Next iSourceRow
End Sub

Answer 1

您不需要使用 set 命令。

使用字符串并将 Range().Value 读入其中

Dim tempChar as string
tempChar = Range(Cells(iSourceRow, 31), Cells(iSourceRow, 31)).Value
msgbox (tempChar)