new at this - please help!

  • Thread starter Thread starter vickie_raven
  • Start date Start date
V

vickie_raven

This is what the macro produced:

ActiveChart.SeriesCollection(1).Values = "='Daily Input'!R6C9:R17C9"



This is what I tried to do - but it gives an error.
I need to substitute the values in the Cells reference with vairables
for the module work correctly.

ActiveChart.SeriesCollection(1).Values = Worksheets("Daily
Input").Range(Cells(6, 9), Cells(17, 9))


Please tell me what I am doing wrong.
 
What the macro recorder produced was a string (something enclosed in a
pair of double quotes such as "abcd"). Further, what it created was a
formula in the form of a string (in XL the leading = starts a formula).
The rest of the formula is a range reference that includes the
worksheet name. By contrast, what you tried to do was get the existing
values of the range.

To do the same effect as the macro recorder you would use
....Values="='" & worksheets("daily input").name & "'!" _
& range(worksheets("daily input").cells(6,9), _
worksheets("daily input").cells(17,9)) _
.address(ReferenceStyle:=xlr1c1)

Simplify the above with
with worksheets("daily input")
....Values="'" & .name & "'!" _
& range(.cells(6,9), .cells(17,9)).address(ReferenceStyle:=xlr1c1)
end with

Finally, if the last cell was not always row 17 but would be the last
contiguous cell below 6,9 that had data, you would use
with worksheets("daily input")
....Values="'" & .name & "'!" _
& range(.cells(6,9), .cells(6,9).end(xldown)) _
.address(ReferenceStyle:=xlr1c1)
end with

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top