header.Offset(x, y).Address causing an error

[VonRobbo]

Hi guys, I'll keep this simple.

The setup:

Dim header As Range

ThisWorkbook.Sheets("Category 1 Tasks").Select
Set header = Cells(Rows.Count, 1).End(xlUp).Offset(47, 0)
'adding the title
header.Select
Selection.Font.Bold = True
With Selection

This works fine:

.Value = "=""3.2.""&(RIGHT(" & header.Offset(3, 3).Address & ",3))&"" ""&" & header.Offset(3, 3).Address

This gives me a syntax error:

.Value = "=""3.2.""&(RIGHT(" & header.Offset(3, 3).Address & ",3))&"" ""&" & header.Offset(3, 3).Address &"" ""&" & header.Offset(4, 3).Address

All I'm trying to do is add some more text to the string that is at header


Regards,
Paul

 

[RoryA]

Welcome to the forum.

You missed a needed quote and an ampersand:

.Value = "=""3.2.""&(RIGHT(" & header.Offset(3, 3).Address & ",3))&"" ""&" & header.Offset(3, 3).Address & "&"" ""&" & header.Offset(4, 3).Address

 

[VonRobbo]

Welcome to the forum.

You missed a needed quote and an ampersand:

.Value = "=""3.2.""&(RIGHT(" & header.Offset(3, 3).Address & ",3))&"" ""&" & header.Offset(3, 3).Address & "&"" ""&" & header.Offset(4, 3).Address

Perfect, Thanks. I was getting confused with all the quoates and ampersands.

 

[RoryA]

It’s easily done!

 

[VonRobbo]

It's happening again.

I'm trying to update series values in a chart based on offset references.

ActiveChart.FullSeriesCollection(1).Values = "=" & header.Offset(1, 32).Address

I'm getting run-time error 1004, even though the header.offset is returning the value I want it to.

 

[RoryA]

You should have a sheet name in there too.

 

[VonRobbo]

You should have a sheet name in there too.

Thanks for your help so far Rory.

I think it doesn't like that there are now double quotes and single quotes as I'm still getting an error.

ActiveChart.FullSeriesCollection(1).Values = "='Category 1 Tasks'" & header.Offset(1, 32).Address

 

[RoryA]

You’re missing an exclamation mark:

ActiveChart.FullSeriesCollection(1).Values = "='Category 1 Tasks'!" & header.Offset(1, 32).Address