format cells as currency and insert formula values

**fredpox** · 03-06-2010, 08:16 AM

Hi there,
I'm moving forward in creating an userform which retrieves data from a worksheet and displays the results in some textboxes.
So far everything works fine, now I need to complete the last step: the user via a combo box selects an item, inserts the quantity and by clicking a button I have to 'send' the selected values to another worksheet in given cells.
I set up this code

Please Login or Register  to view this content.

This code works and place all the data in the form in the right cells, but I'm having a few troubles in set the value for the last two string listed as a formula.
My goal is to set the value as a formula like =E3*F3 which actually returns me the extended volume multiplying the unit volume by the quantity.
Is maybe better to achieve the result by combining simple Excel formula and import via VBA only the unit values?
Either cases I'm getting a 2nd problem: how to format imported VBA data to currency.
Above I used the property Style of the object Cells, referencing which cell I want to format, but even though the code works fine (debug ok), my cells actually wont format.
Of course, I think I don't define well the cells, because the cells formulas as well won't return me the results..
I attach a couple of self-explaining images.

Next step would be increase the row by 1 as I inserted the values, so that next selected item in my userform will be placed below the 1st row.

thanks again for your help guys

P.

**rwgrietveld** · 03-06-2010, 08:51 AM

Looking for something like this? I used range (instead of cells(,) just to give you an alternative.

Please Login or Register  to view this content.

**fredpox** · 03-06-2010, 03:51 PM

Precisely Ricardo! thanks a lot!

I can't try it on my Excel file at the moment but I'll update in brief time.

By the way, how can create a loop so that whenever I insert something with the code above will jump to the next row for the next record?
I tried to put after the last cells(x,x).value a statement like Row = Row+1 but of course when the sub restarts re-reads Row=3 so I still overwrite on that row.

Thanks again for your time.
Have a nice weekend!

P.

**rwgrietveld** · 03-06-2010, 05:10 PM

You might define Row as a static

Try this piece of code

Please Login or Register  to view this content.

**fredpox** · 03-07-2010, 08:03 AM

Hi forum and hi Ricardo

Thanks again for your code!
Both of them work fine.

I just noticed one 'strange' behavior when I insert the data: the cells in which the value is a formula are formatted as currency, as expected, but the cells which contain my PrezzoProdotto (unit price) are formatted as a plain text, well kinda..
If I go to select the cell it returns me the cell type is custom, but I see the little green triangle at the top left of the cell which advice me whether I want to convert the value as a number.
If I ignore the warning and click in the cell and then press enter, my value gets formatted properly as currency, as my code should have told it to.

By the way, if I open my userform and I start to select and insert the data in my worksheet and I close the userform, when I re-open it to keep on inserting new items the row in which puts my new data re-start from 3 (my first item row): is there a way to 'keep track' or check if any row of data is already inserted in order to continue the list?

thanks again
bye
P

PS: I don't know if this is all related to the questions in this thread/post but I also tell you the next steps I'll try to implement in my userform after this 'insert selected data' thing is ok, so maybe if the code may differ we start by defining it from the start.
Here the next steps, triggered by other 2 buttons in my userform:
insert a blank row
insert a row in which is stated a title that I manually insert in a textbox (see the attached image in my #1 post)

**rwgrietveld** · 03-07-2010, 03:44 PM

The formatting of a cell is always
positive;negative;zero;text

if you cell formatting is defined as ##.##;[RED]- ##.##;0;@ than text is displayed differentle as a positive number (mind the green triangle, which is an indication that the cell content is not as expected e.g. a number as a text.

**fredpox** · 03-08-2010, 06:44 PM

Hi Ricardo, and sorry for the delay of this reply.
I'm not sure I got the gist of your last post:
My 'raw' source for unit price is like this: xx.xx i.e. 89.50.
In the row source there isn't any formatting.
When I lookup that value in my userform, and via function I pass it to the appropriate cell in another worksheet I use your formatting code like this

Please Login or Register  to view this content.

I expected the code would have formatted my data to € 89.50 but didn't.
As I previously said, it does only if I click in the cell and press Enter.

By reading your post I understand my value is positive, with the code above I define it to be a value which is preceded by the € symbol, that the grouping of whole numbers will be performed each 3 digits with a comma, and decimals will be divided by a point. Two digits will be allowed for decimals, and one digit for thousands.
I'm still confused on what Excel considers not expected when formats my value.
Am I supposed to format my raw source as well (by hand) prior the vba code will take place?

Thanks again for your time and patience

P.