VBA calc using a named cell

[scottishmovies]

Hi Guys,

I'm a bit stumped (Again!)

I have a cell on a sheet (Settings) named "SALES" and a User Form on another sheet (Data) called UserForm1 with a TextBox which get a number from the user called TextBox10.

Everything works great except that I now want to do a calculation to return to the Data sheet ie TextBox10 * SALES which would hopefully return the calulation of the qty in TextBox10 multiplied by the figure on the Settings sheet caleld SALES - but I can't figure it out - any ideas?

I've tried: Datasheet.Cells(CurrentRow, 10).Value = TextBox10 * SALES
but no luck where am I going wrong?

Regards,
Pat

 

[VoG]

Try

Datasheet.Cells(CurrentRow, 10).Value = Val(TextBox10.Value) * Range("SALES").Value

 

[scottishmovies]

Hi Peter,

Thanks for the very quick reply. I'm getting an error:

Run Time error '424'
Object required

Do I need to define the sheet (Settings) beforehand or in the range?

Pat

 

[VoG]

I assume that DataSheet is an object variable that you have assigned a sheet to. If it is the name of a sheet you would need

Sheets("Datasheet").Cells(CurrentRow, 10).Value = Val(TextBox10.Value) * Range("SALES").Value

 

[scottishmovies]

Went back to basics and set up a new form with your first code in it - and it worked so I suspect I must have something else causing me problems.

But now that I know what to put in for it work - thank you! Much appreciated!

Pat