VBA: Store value to variable

Question

Sub test() ' ' test Macro '

' Dim aRange As Range Dim i As Integer

Set aRange = Range("A1:A255")

Range("A1").Select
For i = 1 To aRange.Count - 1

    If InStr(ActiveCell.Value, "Last name") Then
        Call CopyContents
    End If
    ActiveCell.Offset(1, 0).Select



Next i

End Sub

Sub CopyContents() Dim currentRange As Range Dim genderAndDiscipline As String

Set currentRange = Range(ActiveCell.Address)


'get the gender and dicipline
Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
'genderAndDiscipline = genderAndDiscipline.Split(" ")

End Sub

Hi There, I'm trying to store a cell value in a variable. But somehow it's keep giving mee an compile error. "Object required"

In my opinion I'm telling the variable to aspect a string and the cell is containing a string, as the debugger says.

Could you help me out?

The currentRange is 'A7' here and the cell above is containing a string with '200m men'

The error occures at Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value

Daniel Daniel · Accepted Answer · 2013-05-28T13:08:45

genderAndDiscipline is declared as a string.

The correct way to assign to a string is using Let instead of Set (which is used for assigning objects).

In order to get rid of the error, remove the word Set from the line causing the error, or replace Set with Let.

That is, use one of the following two alternatives (which are equivalent):

genderAndDiscipline = ActiveCell.Offset(-1, 0).Value

or

Let genderAndDiscipline = ActiveCell.Offset(-1, 0).Value

VBA: Store value to variable

3 Answers