Assign formula to a Sheet Cell through a VBA Form

[nimesh.mistry]

Hello

My Problem is:

The Worksheet "Stock" is just for viewing data and no inputs are to be done.
Structure of my excel file.
Upon clicking Add Button, VBA Form opens up. This form adds the data to the next sequence sr. no. row.
It also duplicates worksheet "Blankformat" and renames it to the Sr. No. generated by it in the previous step.
So couple of worksheets names 1,2,3,4,5..... will be generated.

I want to built-in the same VBA Form the ability to assign formula to the "Stock" cells and pull the data from Worksheets 1,2,3,4,5,...

Example

Cell(4,2), Cell(4,3), Cell(4,4), Cell(4,5) etc of WS("Stock") to be assigned formula =C2, =C3, as so on. of WS 1

Appreciate help in this case.

-Nimesh

Help.xlsm

[vandan_tanna]

Nimesh:

you can use following format to assign formula:

Please Login or Register  to view this content.

[nimesh.mistry]

Hello Vandan

Thanks for the reply.

I am aware of this function. But my problem is how should I define SheetName in this case as SheetName is generated by the sub function at the time of clicking the Add button.

Looking forward to some solution.

Thanks

[fredlo2010]

Hi,

Do you want a formula or values. You can tell the code to write values in each tab and then write them in the Summary sheet. or use WS as reference when using the formula R1C1 to populate the summary.

[nimesh.mistry]

Hi Fredlo

I didn't understand what you mean....

My problem is syntax to point SheetName (in my case - NewWS is the SheetName generated) which is String but it doesn't work when used in Cell(R,C).formula equation.

Nimesh

[fredlo2010]

Every time I need to use a R1C1 formula I record it using the macro recorder. You will get it in a blink of an eye.

Then you change it to fit you needs and though it in the loop. I wish I could help more but I am a newbie too