Adding a count function to a formula.

[stojko89]

Hi

I'm using this code:

Please Login or Register  to view this content.

And the .offset(1,4) - Colum E would have values that depend on the cell above.
It starts with Cell E3 and the value of Cell E3 would be D0001 and the Cell E4 would depend on the cell E3 and the value of cell E4 would be D0002 and so on... I tryed doing it with some formulas and I couldn't get it to work right. Would some one help me with this please?

[Steffen Thomsen]

Please Login or Register  to view this content.

Try this

[stojko89]

Ok, so here is what I did:

Please Login or Register  to view this content.

It says Compile Error:

For without Next

[Steffen Thomsen]

Yes., you are missing this line right before "Unload me"

Please Login or Register  to view this content.

[stojko89]

Ok so now it works. But it doesn't enter the next D000* number in Colum E

[Steffen Thomsen]

Try uploading your workbook, and i'll have a look.

It works at my end!

[stojko89]

Ok here goes

I've added a copy of the workbook.

The UserForm is when you click the Novo Narocilo button and then in this userform u have to click Nov Dobavitelj. And when You click Dodaj your code should work for the sheet UserFormInfo
You'll see

Here is the code I'm using:

Please Login or Register  to view this content.

[stojko89]

Maby its not working becose the value of the cell above is D0010 and doesn't have three zeros' like this D0002 ?

Couse I need the code to from
D0001
D0002
D0003
D0004
D0005
D0006
D0007
D0008
D0009
D0010
D0011
So the number of signs stay 6

[Steffen Thomsen]

Ok, found the solution, simple as it is

change this line

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[stojko89]

I saw that it was changing the line above when I was adding a nother company but I was runining around this part of the code:

Please Login or Register  to view this content.

But I had no luck.

But now the code works perfect!

Thank you very much again! :D

[Steffen Thomsen]

Your welcome!

[snb]

or

Please Login or Register  to view this content.

[stojko89]

snb your code works in one way but not quite correct:

It changed all of the numbers from E3 to E58 even though the A colum is filled to cell A13
And the numbers go like this:
D0549
D0549
D0551
D0563
D0563
D0564
D0568
D0570
D0571
D0571
D0575
D0576
D0576
D0578
istead of:
D0001
D0002
D0003
D0004
D0005

But thank you for giving it a swing and a different code
Steffen got the code working correctly for me.

[snb]

In that case

Please Login or Register  to view this content.

[stojko89]

Now it says:

Run-TIme Error 438

Object doesn't support this property or method

And when I hit debug it colors this line:

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[stojko89]

Almost there

Now it does this:

When I hit add and the text is copyed in Cells A5,B5,C5,D5 - E5 stays empty
When I hit add for another company it copys the text under those above and looks like this:
text copyed to A6,B6,C6,D6 - E5 gets the right number but E6 stays empty

So it doesn't add the number in the row it sopose to but later if I add 2 companys and when I add the second company it enters the correct velue for the first company.

[Steffen Thomsen]

Hi,

Actually this would be enough

Please Login or Register  to view this content.

[stojko89]

Steffen it says:
Argument not optional and then it highlights:

Format(Right

Just the word Right.

And I put in the code like this:

Please Login or Register  to view this content.

[Steffen Thomsen]

Sorry, just left out a bit

Please Login or Register  to view this content.

It should be placed outside the with statement you allready have

[snb]

Please Login or Register  to view this content.

If you need a next number you do not need to renumber the whole column again.

[stojko89]

So I don't open a new thread I'll ask here: I'm tryn to copy multiple cells in a textbox in a UserForm if its even possible and I'm doin it like this but I keep geting an error for object not defined:

Please Login or Register  to view this content.

I'll try the 2 codes and I'll let you know how it goes

[snb]

Basically:

Please Login or Register  to view this content.

[stojko89]

snb no luck.

Now it's copying down the name in colum A like this:

A3 = Pahor - and then it makes
A4 = Pahor
A5 = Pahor.....and so on
Colum E stays empty.

Steffen your code doesn't work like this:

Please Login or Register  to view this content.

Says Argument not optional and colors the same part of the code (The word Right)

[stojko89]

snb For the code:

Please Login or Register  to view this content.

It says Type Mismatch.
The code should take every text between Colum A and F and number of rows = untill the text stops.

[snb]

Please Login or Register  to view this content.

[snb]

You can't put a multidimensional range into a textbox, unless:

Please Login or Register  to view this content.

But I don't think you want so much text in 1 textbox.

[stojko89]

snb the code showed me an error again and it highlighted this part:

Please Login or Register  to view this content.

But you don't have to work on this code anymore, I'll use the one that works.
Altough it was fun

[Steffen Thomsen]

And you didn't copy the right code

Please Login or Register  to view this content.

[stojko89]

What if I use a ListBox? Can I somehow put the data from A3:K10 in there?
I don't know what to enter in under the uptions where it says ControlSource:
becose this doesn't work '00057-2011'!A3:K10
And it worked for ComboBox.

[stojko89]

Steffen

It says Synax error for this row:

Please Login or Register  to view this content.

[stojko89]

I hope we can continue tomorrow
I have to go home now. And I don't have this wb at home so I can't do anything untill tomorrow 6am when I come back to work.
So thanks for the help and the effort!

[snb]

at last:

Please Login or Register  to view this content.

[stojko89]

snb! It works! You did it
Now I have to working codes and I don't know wich one to use
Thanks again.

I'll open a new thread for the multirow copy in the textbox or combobox or listbox.

Thank you bouth again, very much