excel - VBA: Store value to variable

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

Answer

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