CODEWITHSUNDEEP
excelvbacompiler-errors
HerrWalter
HerrWalter

Reputation: 632

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

I'm trying to store a cell value in a variable. But somehow it's keep giving me 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.

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

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

Upvotes: 0

Views: 39812

Answers (3)

Daniel
Daniel

Reputation: 13112

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

Upvotes: 4

Gaffi
Gaffi

Reputation: 4367

The Set keyword works with Objects. Since you are looking just to save the value, you should leave this out. i.e. Change:

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

to:

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

Upvotes: 2

StoriKnow
StoriKnow

Reputation: 5866

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

Upvotes: 1

Related Questions