macro to reference a cell on different sheet via a constructed cel ref on a different sheet

[zygoteer]

I am new here - just registered, and relatively inexperienced in Excel (- all self taught, but I so like Excel!).

I have a specific problem with a macro to take a cell ref from a cell value on one worksheet and place a value of 1 in that cell ref on another worksheet - I have tried quite a few wide variations of code from looking online, but keep bumping up against syntax errors all the time - need help - I am obviously missing something ! (probably something quite basic, and for that I apologise if this is trivial to you)

specifically ...
sheet1 - a cell (BF29) constructs a cell address like this: =CONCATENATE("AP",B6+9)
(...where B6 is a variable that, say, contains a value of 101)
I have a macro that when activated needs to put a value into a cell address (from above) but on the Clientele worksheet.
... to achieve something like: .. Range("Clientele!AP110") = 1

Any ideas of the VBA code for this please ?

 

[My Aswer Is This]

So are you trying to take the value in say A1 and put that same cell value in sheet "Clientele" say cell B12?

 

[JLGWhiz]

Sheets("Clientele")Range("AP" & Range("B6").Value + 9) = 1

 

[zygoteer]

Wow what a quick answer ! - impressive

Thanks VERY much it works just fine.

My efforts were similar but not quite the same, and it seems the mistakes I was making were including the '!' after the sheet name and a '.' before the 1st 'Range' - and so was giving errors.

Sheets("Clientele")Range("AP" & Range("B6").Value + 9) = 1

 

[JLGWhiz]

Wow what a quick answer ! - impressive

Thanks VERY much it works just fine.

My efforts were similar but not quite the same, and it seems the mistakes I was making were including the '!' after the sheet name and a '.' before the 1st 'Range' - and so was giving errors.

Something like mixed metaphors, only this was mixed syntax.
Regards, JLG