PDA

View Full Version : [SOLVED] Concatenate VBA that Keeps Applied Formatting



binar
08-28-2011, 08:58 AM
Fellow Forum Members,
I need help making some changes to the VBA script below. The script below makes it posstible to have different formatting in different parts of the result string in column C. This script is necessary because using a Concatenate function that looks like this =Concatenate(A1," - ",B1) results in losing applied special formatting in the results.

My special formatting in Column A and B is as follows:

Column A has old numbers with leading zeroes like: 0074, 0088, 0001, 0754, 0444. These leading zeroes are generated using Portugal Zip Code column formatting. Also this column has special FONT formatting consisting of RED color fonts and a horizontal line across the text known as a STRIKETHROUGH.

Column B has new numbers with leading zeroes generated using Portugal Zip Code column formatting that look like this: 0064, 0068, 0007, 0554, 0344. These numbers have no special FONT formatting applied. They are just BLACK and have no STRIKETHROUGH.

I need the script not to drop from column A and B the leading zeroes from the result in column C. Also not to drop from column A the RED font color and the Strikethrough in the results shown in column C. One last item I also need changed is the dash shown in the code as " - ". I would like to replace it with a carriage return obtained by using the code "CHAR(10)". I have not been able to add a carriage return so that the old red number with the strikethrough appears on the first line of Cell C1 and the new number without the strikethrough appears on the second line of Cell C1. In othe words, it's the same result that is obtained by using Alt+Enter within a cell to divide a text string into two lines within the same cell.


Sub KeepFormat()
Dim r As Range, i As Long
Set r = Range("a1").CurrentRegion.Resize(, 3)
For i = 1 To r.Rows.Count
With r.Cells(i, 3)
.Value = r.Cells(i, 1) & " - " & r.Cells(i, 2)
With .Characters(1, Len(r.Cells(i, 1)))
.Font.Color = 255
.Font.Strikethrough = 1
End With
End With
Next
End Sub



Any help in making the modifications I need done to the script above will be greatly appreciated. Thanks.

mikerickson
08-28-2011, 09:49 AM
Try

With r.Cells(i, 3)
.Value = Format(r.Cells(i, 1), "0000") & "-" & Format(r.Cells(i, 2), "0000")
With .Characters(1, Len(Format(r.Cells(i, 1), "0000")))
.Font.Color = RGB(255, 0, 0)
.Font.Strikethrough = 1
End With
End With

binar
08-28-2011, 07:35 PM
Try

With r.Cells(i, 3)
.Value = Format(r.Cells(i, 1), "0000") & "-" & Format(r.Cells(i, 2), "0000")
With .Characters(1, Len(Format(r.Cells(i, 1), "0000")))
.Font.Color = RGB(255, 0, 0)
.Font.Strikethrough = 1
End With
End With

Mikerickson,
Thanks for your post. I tried out your code and it hangs up on the first line. I noticed that it's missing an opening SUB statement so I added:
"Sub KeepFormat()"
However, this did not work either.

I would really appreciate it a lot if you or anyone else in this forum can help me in getting this code to work.

Lastly, what part of your code performs the "Alt+Enter" / Char(10) carriage return operation to get two lines of text strings in one cell? I don't see it anywhere.

Any help will be greatly appreciated. Thanks. :hi:

mikerickson
08-29-2011, 07:59 AM
There is a section of the originaly posted code that starts with
With r.Cells(i,3) and stops with End With (it must be the matching End With, not just the first one).

Replace that section with the code that I posted.

About the desired line feed. You want to replace "-" with a line feed, Chr(10)

Find the "-" in your code and replace it with Chr(10)

Kenneth Hobs
08-29-2011, 08:05 AM
Try posting a workbook with just the part that you need help with. The .Text property should keep your format as a string. Use vblf as your " - ".

shrivallabha
08-29-2011, 08:05 AM
I guess, you misread mikerickson's reply. Your code should look like:


Sub KeepFormat()
Dim r As Range, i As Long
Set r = Range("a1").CurrentRegion.Resize(, 3)
For i = 1 To r.Rows.Count
With r.Cells(i, 3)
.Value = Format(r.Cells(i, 1), "0000") & "-" & Format(r.Cells(i, 2), "0000")
With .Characters(1, Len(Format(r.Cells(i, 1), "0000")))
.Font.Color = RGB(255, 0, 0)
.Font.Strikethrough = 1
End With
End With
Next i
End Sub

binar
08-29-2011, 06:32 PM
Shrivallabha, Ken and Mikerickson,
Thanks to all of you for your posts. This VBA project was a great learning experience. I managed to get it to work with the code postings shared between Shrivallabha's and Mikerickson. I just added the "Chr(10)" instead of "-" and I got the carriage return I was needing to work. This script does something that apparently can't be done using a formula. I am amazed with how much more powerful VBA coding is compared to using formulas. Again, thanks to all for the help. :hi:




I guess, you misread mikerickson's reply. Your code should look like:

Sub KeepFormat()
Dim r As Range, i As Long
Set r = Range("a1").CurrentRegion.Resize(, 3)
For i = 1 To r.Rows.Count
With r.Cells(i, 3)
.Value = Format(r.Cells(i, 1), "0000") & "-" & Format(r.Cells(i, 2), "0000")
With .Characters(1, Len(Format(r.Cells(i, 1), "0000")))
.Font.Color = RGB(255, 0, 0)
.Font.Strikethrough = 1
End With
End With
Next i
End Sub