dawber39
asked on
Excel VBA
What I am trying to do is write a macro that applies a formatting to the the B colum n from cell 2 down. It will duplicate the value in column a - but add dashes to it - like from 1234567891234 to this 12345-67891-234 in that format too 5/5/3
This is what I have - and I have changed it many times already
appreciate your help
This is what I have - and I have changed it many times already
appreciate your help
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
Dim istart As Long
Set i = 2 - 1000
Set istart = B2
Do Until Record Is Null
Sheets("ProdAtt").Range.istart = Mid(A, 1, 5) & "-" & Mid(A, 6, 5) & "-" & Mid(A, 11, 3)
Loop
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So you want the 13 digit value in column replicated in column B but with the dashes inserted.
Why not just the MID function like you have done in the code but in a formula in column B? As column A expands a simple double click of the bottom right hand corner of the last formula in column B will populate the rest of the cells in column B.
Alternatively, you can use Custom format, either in column A or in column B. If in column B the formula in B would simply be =A2 and then custom format would be:
00000"-"00000"-"000
The dashes would only appear in the formatting not in the cell contents.
Thanks
Rob H
Why not just the MID function like you have done in the code but in a formula in column B? As column A expands a simple double click of the bottom right hand corner of the last formula in column B will populate the rest of the cells in column B.
Alternatively, you can use Custom format, either in column A or in column B. If in column B the formula in B would simply be =A2 and then custom format would be:
00000"-"00000"-"000
The dashes would only appear in the formatting not in the cell contents.
Thanks
Rob H
ASKER
Perfect - Thank you
ASKER
Awesome as always
I agree with Rob that - if you didn't absolutely need the new, 15-character string with the hyphens as a value - either using a concatenated function or a custom format would work:
in cell B2, copied down:
=IF(LEN(A2)=13,LEFT(A2,5)& "-"&MID(A2 ,6,5)&"-"& RIGHT(A2,3 ),"Not a valid value")
or
apply this custom format:
#####-#####-###
Only problem with the custom format is that it formats right-to-left, so if the value in column A has anything other than 13 characters, you'll get an undesirable result. Also, the underlying value will still be a number and not a string.
Regards,
-Glenn
in cell B2, copied down:
=IF(LEN(A2)=13,LEFT(A2,5)&
or
apply this custom format:
#####-#####-###
Only problem with the custom format is that it formats right-to-left, so if the value in column A has anything other than 13 characters, you'll get an undesirable result. Also, the underlying value will still be a number and not a string.
Regards,
-Glenn
You would like a macro to automatically populate a formatted value in column B whenever a new value in column A is added or changed. The value in B should be of the following format:
xxxxx-xxxxx-xxx
1) What is "Record"? There is no built-in name or operation in VBA for this.
2) What happens if the value in A is not exactly 13 characters long?
3) Do you want this to run every time ANY cell is changed on the worksheet?
Regards,
-Glenn