How can you shift a Defined Name using Excel VBA?

Question

I am trying to automate a rolling calendar spreadsheet that tracks various metrics. The script I would like to write would shift the selected range in a named each time it is ran.

I did have success incrementing the spark lines using this macro:

If Sheet2.Range("E5").SparklineGroups.Count > 0 Then
    With Sheet2.Range("E5").SparklineGroups.Item(1)
        .SourceData = "'" & Range(.SourceData).Parent.Name & "'!" & Range(.SourceData).Offset(, 1).Address
    End With
End If

However, I am stuck referencing the defined name Hzrange using a similar macro below. I am very new to VBA and any help is appreciated as always.

If ActiveWorkbook.Names("Hzrange").RefersToR1C1 > 0 Then
    With ActiveWorkbook.Names("Hzrange")
        .RefersToR1C1 = "'" & Range(.RefersToR1C1).Parent.Name & "'!" & Range(.RefersToR1C1).Offset(, 1).Address
    End With
End If

What is the problem with your code? what error do you get or what problem results from running it? — RBarryYoung
– RBarryYoung, Commented Aug 28, 2018 at 16:36

QHarr · Accepted Answer · 2018-08-28 16:22:04Z

0

Why not

If ActiveWorkbook.Names("Hzrange").RefersToR1C1 > 0 Then
    With ActiveWorkbook.Names("Hzrange")
        .RefersToR1C1 = Range("Hzrange").Offset(, 1)
    End With
End If

answered Aug 28, 2018 at 16:22

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

Collectives™ on Stack Overflow

How can you shift a Defined Name using Excel VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related