Concatenated the values of cells in same cell but keeping the font of the last cell as free30f9(barcode)

[jhonty4]

Hi, the objective of my project was to generate labels from an excel file which will be downloaded from ERP software. With the help of mr excel i was able to do everything that I wanted to and even generate the labels. Now the final nail in the coffin is that the cell G which contains the barcode is not displaying as barcode but in the same font as all the other values. Is there any way that can make sure that the objective can be achieved by printing the labels with the barcode
If the fonts in the same cell cannot be different we could also try to divide the cells and print barcode into that. Column G contains the barcode. TIA

Sub AveryLabels4014()

''''    Print onto Avery Compatible 4014 labels (1 7/16" x 4")
''''    Caveat:  You may need to adjust row height/column width and/or margins to fit your labels


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim LastRow As Long, i As Long, j As Long, NoCols As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Filtur As String
Dim prtArea As Range


Set ws1 = ActiveSheet
Set ws2 = Sheets.Add(after:=Worksheets(Worksheets.Count))
LastRow = ws1.Cells(Rows.Count, "E").End(xlUp).Row
Filtur = InputBox("Please Enter Filter Criterion")


''''    Filter and copy data to new sheet
On Error GoTo errHandler:
With ws1
    .AutoFilterMode = False
    .Range("A6:G6").AutoFilter
    .Range("A6:G6").AutoFilter Field:=1, Criteria1:=Filtur
    .Range("B7:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy ws2.Range("B1") 'this needs to be changed such that the Column G contains barcode font
    .AutoFilterMode = False
End With


''''    Concatenate data to Column A
LastRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row
With ws2
    For i = 1 To LastRow
        .Cells(i, 1) = .Cells(i, 2) & vbCrLf & .Cells(i, 3) & vbCrLf & _
                       .Cells(i, 4) & vbCrLf & .Cells(i, 5) & vbCrLf & _
                       .Cells(i, 6) & vbCrLf & .Cells(i, 7)
        .Cells(i, 1).Value = Cells(i, 1).Value
    Next i
    Columns("B:G").Delete
End With


''''Resize to one across
j = 1
NoCols = 1
For i = 1 To LastRow Step NoCols
    ws2.Cells(j, "A").Resize(1, NoCols).Value = _
    Application.Transpose(ws2.Cells(i, "A").Resize(NoCols, 1))
    j = j + 1
Next
Range(ws2.Cells(j, "A"), ws2.Cells(LastRow, "A")).Clear
LastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Set prtArea = ws2.Range("A1:C" & LastRow)


''''    Format cells
With ws2.Cells
    .RowHeight = 103.5 'Adjust as necessary
    .ColumnWidth = 54.57 'Adjust as necessary
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
End With


''''    Set Print Settings
Application.PrintCommunication = False
With ws2.PageSetup
    .LeftMargin = Application.InchesToPoints(0.01) 'Adjust as necessary
    .RightMargin = Application.InchesToPoints(0.01) 'Adjust as necessary
    .TopMargin = Application.InchesToPoints(0#)  'Adjust as necessary
    .BottomMargin = Application.InchesToPoints(0#)  'Adjust as necessary
    .Orientation = xlPortrait
    .PaperSize = xlPaperLetter 'adjust to match your printer type
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .PrintArea = prtArea.Address
End With
Application.PrintCommunication = True


Range("A1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub


errHandler:
    ws1.AutoFilterMode = False
    ws2.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Sorry, your filter criterion did not find a match." & vbCrLf & _
        "Please try again."
End Sub

 

[pgc01]

Hi

The text in the cell can have more than 1 font.

Can you post some examples of the content of a cell in column G and exactly which are the characters that you want in each font?

 

[jhonty4]

Hi pgc01 , The content of a cell in column G is *BUTTON-SR#36975/16-ALL-ALL* . This should be in the font free 3 of 9 to convert it into barcode. The cell content already has an asterisk in start and in the end.

 

[pgc01]

Hi

You said in your first post that part of the cell would be in one font and part in another.

The example you posted "*BUTTON-SR#36975/16-ALL-ALL*" has 28 characters.
If I understand correctly you want some of these characters to be printed with the font named "Free 3 of 9".
Which of the 28 characters in the string do you want in this font?

 

[jhonty4]

NO Everything that I posted "*BUTTON-SR#36975/16-ALL-ALL*" has to be in font named "Free 3 of 9" others can be any font. The code above is copying data from Column B to G and concatenating it into one cell. Out of these 6 columns concatenated in one single cell, I only want the content of cell "G" in the font named "Free 3 0f 9".
This "*BUTTON-SR#36975/16-ALL-ALL*" is an example of data present in column "G" which will always start and end with asterisk. I hope I am clear.

 

[pgc01]

In that case, if it's all the contents of the cells in column G, just add to your code before the End With:

Columns("G").Font.Name = "[COLOR=#333333]Free 3 of 9[/COLOR]"

 

[jhonty4]

The content of cell g is already in free30f9 font. The code I posted above is pasting it as a value in new sheet and then concatenating it. That is why it is not showing as barcode. If you could tell me how can i change the font after it has been pasted then I might be able to try. Thanks for your time

 

[pgc01]

When you copy the cell it will also copy the format (unless you tell it not to).

It's the concatenation that can change the format.

How are you concatenating?

If it is with a formula then the answer is no. You cannot have 2 formats in the cell contents if the contents are the result of a formula.

If you are concatenating with vba, then yes, you can have several formats in the contents of 1 cell.

Please tell exactly how you do it.

Let's say you copy Sheet1!G3 to Sheet2. Now what do you do? What do you concatenate? Is it a string from vba? Is it the contents of another cell?
Please post a complete example.

 

[jhonty4]

Hi PG I have used a code picked from mr excel that is why i am not very clear on how it is doing it. I am posting the code which copies the data and concatenates it. Hope this will answer your question.

''''    Filter and copy data to new sheet
On Error GoTo errHandler:
With ws1
    .AutoFilterMode = False
    .Range("A6:G6").AutoFilter
    .Range("A6:G6").AutoFilter Field:=1, Criteria1:=Filtur
    .Range("B7:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy ws2.Range("B1") 
    .AutoFilterMode = False
End With


''''    Concatenate data to Column A
LastRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row
With ws2
    For i = 1 To LastRow
        .Cells(i, 1) = .Cells(i, 2) & vbCrLf & .Cells(i, 3) & vbCrLf & _
                       .Cells(i, 4) & vbCrLf & .Cells(i, 5) & vbCrLf & _
                       .Cells(i, 6) & vbCrLf & .Cells(i, 7)
        .Cells(i, 1).Value = Cells(i, 1).Value
    Next i
    Columns("B:G").Delete [COLOR=#333333]End With[/COLOR]

 

[pgc01]

Hi

OK. Now I understand. In each row you want to concatenated the values in the columns B:G into 1 cell but you want the characters in G to be in another font in the result.

I don't have time to study your code now, but this is a simple test you can try that I think is like you want.

Let's say we concatenate the values in B2:G2 into B4. In the resulting text in B4 we want the characters in G2 to be displayed with the font Arial, size 36

try:

' concatenates B2:G2 to B4
' the text in G2 appears in the result with the font Arial size 36
Sub Test()
Dim l As Long

' first concatenates B2:F2 into B4and gets the length of the text
Range("B4").Value = Range("B2").Value & Range("C2").Value & Range("D2").Value & Range("E2").Value & Range("F2").Value
l = Len(Range("B4").Value)

' now adds the text in G2
Range("B4").Value = Range("B4").Value & Range("G2")

' change the text that came from G2 to the font Arial size 36
With Range("B4").Characters(l + 1).Font
    .Name = "Arial"
    .Size = 36
End With
End Sub

Do this simple test:

1 - write in B2:G2 the strings

bb cc dd ee ff gg

in some font with the usual size 10-12

2 - run the code



You'll see the result of the code B4 something like

bbccddeeffgg

You'll see that the last characters, "gg", are in font Arial and with size 36. These were the characters that originated from G2

This seems to me is similar to what you want.

Please try.