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

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

Where does the error occur?

Christian Sauer
– Christian Sauer

2013-05-28 13:03:30 +00:00
Commented May 28, 2013 at 13:03 — Christian Sauer
– Christian Sauer, Commented May 28, 2013 at 13:03
Try removing the Set keyword from that line

John Bustos
– John Bustos

2013-05-28 13:09:35 +00:00
Commented May 28, 2013 at 13:09 — John Bustos
– John Bustos, Commented May 28, 2013 at 13:09

Yane · Accepted Answer · 2019-04-25 06:22:16Z

4

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

edited Apr 25, 2019 at 6:22

Yane

8378 silver badges18 bronze badges

answered May 28, 2013 at 13:08

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gaffi · Accepted Answer · 2013-05-28 13:09:56Z

2

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

answered May 28, 2013 at 13:09

Gaffi

4,3758 gold badges47 silver badges73 bronze badges

Comments

StoriKnow · Accepted Answer · 2013-05-28 13:09:29Z

1

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

answered May 28, 2013 at 13:09

StoriKnow

5,8676 gold badges40 silver badges47 bronze badges

Collectives™ on Stack Overflow

VBA: Store value to variable

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related