Application.Transpose(var) excel 2007 and 2010

[Stingone]

Hi everyone.

Im using the code below to change a row in a listbox. As i'm using cellformatting i'm using the Application.Transpose(var) what is working fine in excel 2010 and showing when putting in 3% changing to 3.0% automaticly. When i'm only using = var it put it in as a text and not as a value.

But when i test the code below in excel 2007 it takes the first value from the row and copy's it in the other 6 columns in the rows.. when can be wrong?

Please Login or Register  to view this content.

[Norie]

Why are you transposing?

You shouldn't need to if the values are going in a row across columns.

Transposing is actually the reason the first value is repeated.

As for the text thing, you've declared var as an array of strings and you are populating it from textboxes which contain strings.

You'll need to change that so the percent and any other numeric values are converted appropriately.

[Stingone]

Why are you transposing?

You shouldn't need to if the values are going in a row across columns.

Transposing is actually the reason the first value is repeated.

As for the text thing, you've declared var as an array of strings and you are populating it from textboxes which contain strings.

You'll need to change that so the percent and any other numeric values are converted appropriately.

I changed the string in a variant. This works BUT.... when it show in my textbox a value of 10.000 and i update the cell it converts it to 10 when i change 10.000 to 10,000 it converts it to 10.000. But when i go to my worksheet and fill in 10.000 it works fine. When i just remove the dot and type in the field 10000 it works also fine. But sometimes you only want to change 1 value on the row of 6 and dont want to retype the existing value's. So i'm little bit confused here

[Norie]

Sounds like VBA isn't recognising the . as a thousands separator, seeing it as a decimal separator.

Try removing the . from the value.

[Stingone]

Sounds like VBA isn't recognising the . as a thousands separator, seeing it as a decimal separator.

Try removing the . from the value.

I know but the thing is when i load a row of 6 values i dont want to remove every time the dot. Sometime you only want to update 1 value in the row.

My cell formatting is using #.##0_);(#.##0);-_) The thing is i used code below that work but took almost 3 second to update the value. With this code it take split second.

Please Login or Register  to view this content.

[Norie]

I'm a little confused, the code you posted appears to be for putting a row of data on a worksheet.

There's nothing in it for single values.

Also, why wouldn't you remove the . if it needs to be removed?

[Stingone]

I'm a little confused, the code you posted appears to be for putting a row of data on a worksheet.

There's nothing in it for single values.

Also, why wouldn't you remove the . if it needs to be removed?

Hi Norrie. Lets tel in steps what i like to have:

I'm using a worksheet with value's. I have created a listbox that pulls the value's from that worksheet in the listbox. Then i have 6 textboxes that will be populated with the values of a row when clicking on a row in the listbox. So when i have on a row for example 100.000 - 1 - 200.000 - 2 - 5 - 0,3 and i like only to change 100.000 tot 125.000 then i also at the moment need to change the 200.000 to 200000 otherwise it will turn the 200.000 shown in the textbox to 200.

I don't bother to see 100000 in the textboxes but i still want to see the 100.000 in the listbox. if that solves the issue.

But some how it is not using the system seperator but using the US standard instead.

[Norie]

How would the 200.000 in the textbox turn into 200?

What I'm suggesting wouldn't do anything to the values in the textboxes, it's to deal with those values when they are transferred to the worksheet.

Could you attach a sample workbook?

[Stingone]

How would the 200.000 in the textbox turn into 200?

What I'm suggesting wouldn't do anything to the values in the textboxes, it's to deal with those values when they are transferred to the worksheet.

Could you attach a sample workbook?

See example... other thing is when i update the listbox it pop back to top left.Example_listbox.xlsm

[Norie]

Every seems to work fine for me.

I can change values like 10,002 to 20,003 and the correct values appear on the worksheet and in the listbox after I've clicked the update button.

[Stingone]

Every seems to work fine for me.

I can change values like 10,002 to 20,003 and the correct values appear on the worksheet and in the listbox after I've clicked the update button.

I know that works but we are using in the NL:

200000 = 200.000
and we use a , as decimal seperator

And that wont work because as soon i fill in 200.000 = 200000 VBA converts it to 200 so it sees the dot as a decimal seperator.

[Norie]

Why not try my suggestion?

Please Login or Register  to view this content.

This will remove the . if any values have it.

When I enter 20.123 and then update everything appears fine.

By the way, why are you reloading the listbox when updating?

Why not just update the row in the listbox as you are the row on the worksheet?

Please Login or Register  to view this content.

[Stingone]

Hi Norie,

Works great only issiue is that updating only the row works great and fast but it then shows -35000 instead of (35000) when i reload the whole listbox then it show again (35000).. is there a quick thing to show also the cell property as the update listbox command?

Thanks again.

Please Login or Register  to view this content.

[Norie]

It should be possible to fix that with Format.

I'll check it out and post back.

[Stingone]

It should be possible to fix that with Format.

I'll check it out and post back.

Hi Norie,

The second if statement does seems not to be the problem. But i can only update 1 row a time. So i need to close the screen and reopen to change another row. If not the data will be placed in the wrong cells when updating a second row. Thanks again. Realy appriciate your help