Home |
Search |
Today's Posts |
#1
|
|||
|
|||
new at this - please help!
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. |
#2
|
|||
|
|||
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 In article , says... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|