Video B. Jelen & Excel Expert reviewing VBA Programming

[DougStroud]

I saw a video of Bill interviewing and covering a program that one of the Excel experts has developed to program in the VBA environment. I can't recall the video or the other details.... anyone seen this and can point me there?

thanks,

 

[MrExcel]

I believe you are talking about the JMT Utilities:

http://jmt.puremis.net/jmtutils/

This was podcast 478 on Tuesday, April 3rd 2007.
http://www.mrexcel.com/podcast/2007/04/episode-478-utilities-for-excel.html

 

[DougStroud]

Perfect-
Thanks Bill.
Podcast 578 : http://cache.libsyn.com/mrexcel/podcast578.mp4
Here is a slight variation of the concatentation function....
Depending on the Mike's use- your point about unprintable characters can be avoided by using this method. Choose the row w/ the greatest number of columns w/ values, enter the formula once:

=W2&" " &X2&" "&Y2&" "&Z2

copy up and down. This avoids printing issues, unpleasent visual issues etc..... and I use the notepad all the time for things of this nature, but no reason to do it on this one IMO.

One other question, on the podcast blog site, you reference 897 a couple of times.... significant date, you start using Excel, MrExcel....?

Cheers,

Doug

MasterImportSheetWebStore.xls
	W	X	Y	Z	AA	AB	AC
1	Term	Term	Term	Term	Results	Formula	Values
2	test	test1				testtest1	testtest1
3	test	test1	test2			testtest1test2	testtest1test2
4	test	test1	test2	test3		testtest1test2test3	testtest1test2test3
Table

 

[MrExcel]

That particular podcast about the concatenation as generated a lot of mail. Everyone has a slightly different solution to that problem. I think I have gotten at least six different solutions, some of which appear in subsequent podcasts and some are still in the queue.

I frequently get a couple of notes saying "there is a better way to do this", but the fact that this podcast generated so many different solutions tells me that this is a major pain point with a lot of people and that there are a large number of home-grown solutions.

I like your method above. If I remember the original question, though, I don't think that he is leaving a live formula in the workbook. He concatenates and then pastes values. I think the hassle factor of re-building the formula each day would not be good. Perhaps he could tuck this formula somewhere out in Z1 and copy from there?

Due to some upcoming trips, I've pre-recorded the next 2 weeks podcasts. Out of the solutions that I've received, the couple lines of VBA code in podcast # 587 on Thursday September 6th seem like the solution that I would actually use if I had to do this task day after day.

Regarding 897: I have no idea why this comes up. I did a quick search and could not find it at all. It doesn't ring any bells as a significant date or number. Probably just randomness.

 

[MrExcel]

Doug-

I would modify your formula with by wrapping an =TRIM() around the whole formula. That will get rid of the extra trailing spaces at the end.

Visually, there is no difference, but you never know if this will later be used in a MATCH or VLOOKUP and those trailing spaces could cause problems later on.

Bill

 

[DougStroud]

Thanks for the response Bill. I agree, and gathered that you would have a solution for the question.... You Da' Man Baby! But I think it is great to have the interaction that you have w/ the boardmembers and so for that reason mainly, I shot it to you. It is also great that you keep in touch w/ all of us here. I soon will be re-launching my website w/ all the new great features I have been able to incorporate from Excel. It is all due to your site and the members here. I will send you the link when it is completed.

Had I never found MrExcel or you starting this I would have never been able to do what I have done. When I re-launch, I will send you the link. The thing that is most interesting about this is the interconnectivity I have been able to achieve due to Excel, its features and MrExcel. The interesting thing is my POS is from the early 1980's, is DOS and written n Cobol. I have very strict limitations to what I can do w/ it, Item Record length and Item Description length limitations, a two level dept/cat limitation- but I now can take all this and convert it into vastly improved data.
You probably have heard this before, but what you have done here is a great thing. I will always be a loyal member and Fan! You have literally changed my life, and professional career- Thanks for all that you have contributed to us here at MrExcel.

Cheers,

Doug Stroud

 

[DougStroud]

Yep.... I kept it simple how you discussed it in the podcast for continuity reasons.... and the other thing that comes to mind if Mike actually did use this method, heck I would build the thing out an extra 20 columns and never look back, stick the Trim function in, store it up one row above everything else and roll on...
But as you said, you have a VBA solution coming...

 

[MrExcel]

Doug - thanks for your notes.

The community of people here are amazing and I always promote them in my seminars.

The cool thing - if there are 100 people in the room, I will always have half a dozen people who confirm that I am 100% correct. Someone will always have a story about how the MrExcel community made something amazing possible.

Again - thanks to everyone who contributes here.

 

[DougStroud]

Bill,
One more thing, the 897 question. It is a graphic item, one oversized one at the top and one small one on the left margin in khaki green under the orange graphic on the podcast site.

 

[MrExcel]

Yes - now I see the 897.

It must have been built in to the canned blogger template. I think when I set this blog up, I believed we would never use the blog, just the RSS feed to get the podcast into iTunes. However - since iTunes shows 50 episodes and I am now up to almost 500 episodes, the blog has become a useful place to find old episodes.

So - some random template designer probably lives at 897 Main Street? I really have no idea.