using macro to reorder and combine

[kellem80]

I'd like to create a macro that will take the information from the first two columns reformat and create the third column as shown formatted. Any help would be appreciated.
<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=240 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=72>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=68>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=100>Number</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num="40425.384027777778">9/4/10 9:13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>Bob Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>100904-0913BS</TD></TR></TBODY></TABLE>

 

[pgc01]

Hi kellem80
Welcome to the board

Try in C2:

=TEXT(A2,"yymmdd-hh:mm")&LEFT(B2)&MID(B2,1+FIND(" ",B2),1)

 

[Joe4]

Welcome to the Board!

You can do this with a simple Excel formula (no macros necessary).
Assuming that your values are in A2 and B2, column A is formatted as date/time, and you just have your values wrapping and not using soft carriage returns, this formula would work:

=TEXT(A2,"yymmdd-hhmm")&LEFT(B2,1)&MID(B2,FIND(" ",B2)+1,1)

 

[kellem80]

Thanks, works great but one more question, what if column A is the formula =now() but I want column C static? I think this is why I believed I needed a macro......

 

[Joe4]

Very similar in VBA:

Range("C2") = Format(Range("A2"), "yyyymmdd-hhmm") & Left(Range("B2"), 1) & Mid(Range("b2"), InStr(Range("B2"), " ") + 1, 1)

Or just use the earlier formulas we provided and use Copy -> Paste Special -> Values to convert those formulas to hard-coded values (can use Macro Recorder to get VBA code for that).

 

[kellem80]

Very similar in VBA:



Or just use the earlier formulas we provided and use Copy -> Paste Special -> Values to convert those formulas to hard-coded values (can use Macro Recorder to get VBA code for that).

OK, completely new to VBA, so bear with me---got nothing from what you provided me. All this is for a form, so I want the user to only enter their name, the date will be auto fill from the =now(), then the formula will creat a unique code for the entry combining date and name information; so the copy paste special, not an option.
Tried the recorder and didn't really get what I think I want. It would be best if the conversion happened on save (so when they save the sheet it turns the date and time to a static date and time)



Most of my experience with macros has been editing ones previously set up for me, so please be kind, and bear with me.

 

[Joe4]

It is really quite frustrating when you keep giving us a little bit of pertinent information with each succeeding post instead of asking the full, real question up front. Your question now is much different than your original post.

 

[kellem80]

It is really quite frustrating when you keep giving us a little bit of pertinent information with each succeeding post instead of asking the full, real question up front. Your question now is much different than your original post.

No need to be so rude, really, you're a moderator? I didn't know about the date formula, which changed my original post, until after I posted and tested the reponses. So yes, it was different, but I DID know that starting a new thread is discouraged, so I continued with my original thread and expanded----as I have seen numerous folks do without being "put in their place" so to speak.

Wow, what a welcome to the forum.

 

[Joe4]

I know I am a Moderator, I am merely stating facts, much like you were when you stated that you "got nothing from what you provided me". Where is the rude part?

We are simply trying to help you, but it is very tough when the question keeps changing. Twice now we thought that we had answered the question, only to find out that what you had asked really isn't exactly (or completely) what you are looking for.

Had I known the extent of your question from the beginning, I probably would not have replied, as I work very little with Excel forms myself, and would have left it to someone with more expertise in that area. So the dangers in asking general or vague questions up front, is that you might not be attracting the right people to deal with your real, more detailed issue.

I realize that you are a new member here, and am trying to guide you in how to use this board to get the most out of it that you can.

The answers that are provided can only be as good as the questions that are asked. So the key to getting a good answer that works for you is to ask a good, complete questions that explains exactly what it is you are trying to do.

If you are working with an existing form that already has VBA code behind it, it would probably be best to post that here too. I am thinking it probably makes more sense to do this calculation at the time the data is entered through the form, rather than upon saving the worksheet. Would you agree?

 

[kellem80]

Form is still in worksheet, no VBA whatsoever. The UID I'm trying to create through this combination of Date/time & initials would be the first piece at this point.
To address your question, it doesn't matter if it is upon save or upon entering, but what then would prompt it changing the UID to static (if not "save"), since upon open the date/time will populate automatically, are you thinking it would it be the entering of the Name?



btw, my comment would have better read, "I tried what you provided but got nothing back from excel as a result", meaning, that I entered what you provided me both in the VB editor and just trying in the cell itself and nothing happened either way. Not sure I was entering your logic correctly.