VBA: Substitute or Replace A with A2 and H with H2

[onlymeami]

Hello I've been searching and using the forums for a while but my search fu is weak so I will ask.

I am working on a document that has users enter the letter A or H in a column. Is there a way to replace or substitute that letter with the A2 or H2 respectively. It's not a huge deal but makes the form look nicer.

Thanks

 

[AliGW]

Is this a query about a user form?

 

[onlymeami]

It's a form I made for users to enter Data as it happens. To track temperatures and gases. Some of them Type A2 or H2 but some just put A or H this is just a pet peave of mine. I can get the function to work but I am trying to find a way to put it in VBA so when they enter A it's automatically replaced with A2 and H with H2.

=SUBSTITUTE(G24,"A ","A2") I can do it with "A " and a space but was just curious if anyone could help

 

[AliGW]

You don't actually need the space! This will work:

=SUBSTITUTE(G24,"A","A2")

But if you want a VBA solution, then you probably need to edit your opening post title to add VBA to it. If you are unable to do so, then use the report button under your opening post and ask a mod to amend it for you.

 

[onlymeami]

:-\ Well I found IF function that works

=IF(G24 = "A", "A2"," ")

Now to apply it to the Sheet

 

[onlymeami]

Thanks I'll have to do that! I was hoping to figure out a VBA solution

 

[AliGW]

You will definitely need VBA because you cannot have both a formula and a value in one cell. Someone will be able to help. Just be patient!

 

[Rick Rothstein]

Thanks I'll have to do that! I was hoping to figure out a VBA solution

Give this a try...

Sub ExpandAandHtoA2andH2()
  Columns("G").Replace "A", "A2", xlWhole, , False
  Columns("G").Replace "H", "H2", xlWhole, , False
End Sub

 

[onlymeami]

Well after overthinking things as I usually do I figured it out Sorry if someone has another solution that's great.

I created two entries A2 and H2 in G24 and G25 and hid the Rows and with the document protected users can just enter "a" and hit enter the "2" is highlighted with auto-fill. I can't believe I never thought of that.

 

[AliGW]

Nice one!