Concatenate range

[DFrank231]

At work we sometimes have to input a long string of concatenated numbers into a field in our information system (ex: 1,2,3,4,5,6,7,8,9,10...etc).

I found a code to accomplish this as follows:

Please Login or Register  to view this content.

And this works, but i want to make it more user friendly for my coworkers, whereas they click on a button (Button10_Click) and it prompts them with an inputBox asking for the range, which they can select with their mouse. It saves this range, and then enters it as the substrings into that concantenation script above. it has been a while since my VBA coding days and i kind of forget where to go from here.

any suggestions would be appreciated.

Thanks everyone.

[pike]

hi DFrank231
Welcome to the forum

Try ...

Please Login or Register  to view this content.

[foxguy]

You're going to have a problem having the macro allow the user to select a range while the input box is on the screen.

My hunch is that you will have to have a button launch a modaless form with 3 buttons on it.
The user would select the range with the data in it, then click button 1.
The user would select the cell where the results go, then click button 2.
The user would then click button 3 to run the macro that concatenates the range and puts the result in the selected cell.

If that sounds like what you want, I can get you started. I won't write the whole thing, but I would created the skeleton and you could just fill in the blanks.

[DFrank231]

Thanks for the responses.

foxguy, that could theorhetically work. you said i would have trouble selecting a range, but i was able to select one previously when i was messing around...but i couldnt figure out where to store that range and then pull it into the concRange macro.

pike: where would i incorporate Button10_Click to run the input box?

I'm sorry if this seems so basic, i havent coded VBA for 8 years but im trying to get back into it.

I attached the file i am working on if that helps.

[DFrank231]

nevermind! figured it out. thanks Pike.

[DFrank231]

Now i am running into another problem...some values being input are too much for the code to handle and it is returning an improper amount....The attachement "error" shows what happens. I run a similar thing with numbers of different lengths and it works just fine (see correct.gif attached).

The code i use is as follows...do i need to declare the TempConcRange variable as something else?

Please Login or Register  to view this content.

[pike]

do you have strings longer than 255

http://support.microsoft.com/kb/105416

[Marcol]

Excel is reading your concatination of numbers as a number.
If you format Range("E9") as text you'll get the expected result.

[DFrank231]

Thanks Marcol. That seems to have fixed it.