Help with adding formula under every cell in ColumnA where it contains data

[Anfinsen]

Good morning all!
I have data in ColumnA, with what can be, a random, number of blanks between them. I wanted to format the cell directly under any cell in columnA where data exists, to use "Free 3 of 9 Extended" font. The code below works beautifully for that, and only formats the cells directly below the cell where data exists, in columnA.

Sub AddBarcodeToPart()
Dim c As Range, sh1 As Worksheet
Set sh1 = Sheets("Pick List")
For Each c In sh1.Range("A6", Cells(Rows.Count, "A").End(xlUp))
If c.Value <> "" Then

c.Offset(1, 0).Font.Name = "Free 3 of 9 Extended"

End If
Next

End Sub

However, when I try to use a similar code, to add the formula needed to make it a readable barcode, it adds the formula to every cell instead of the just the offset reference. (It also doesn't add the needed quotes around the * correctly to make this a readable barcode).

Sub ValueForBarcode()
Dim c As Range, sh1 As Worksheet
Set sh1 = Sheets("Pick List")
For Each c In sh1.Range("A6", Cells(Rows.Count, "A").End(xlUp))
If c.Value <> "" Then

c.Offset(1, 0).Formula = "*" & c & "*"

End If
Next

End Sub

Any ideas what I could be doing wrong here? (I am sure there are probably better ways to achieve my desired result, but this is my starting point)
Thanks in advance for any help!

 

[Candyman8019]

Can you confirm that the cells you think are empty actually are, ie no spaces, etc.

As for your formula, try using double quotes...something like this: c.Offset(1, 0).Formula = "=" & ""*"" & c & ""*""

 

[DanteAmor]

Hi @Anfinsen.
Thanks for posting on the forum.

I'm not quite sure what formula you want to put, but I'm assuming it's something like this:

Sub ValueForBarcode()
  Dim i As Long, sh1 As Worksheet
  Set sh1 = Sheets("Pick List")
  For i = sh1.Range("A" & Rows.Count).End(3).Row To 6 Step -1
    With sh1.Range("A" & i)
      If .Value <> "" Then
        .Offset(1, 0).Formula = "=""*""&" & .Address(0, 0) & "&""*"""
      End If
    End With
  Next
End Sub

If indeed after each cell with a value there is a space, then this can also be the case, the following code is smaller and works faster.

Sub ValueForBarcode_v2()
  Dim ar As Range, sh1 As Worksheet
  Set sh1 = Sheets("Pick List")
  For Each ar In sh1.Range("A6", sh1.Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    ar.Offset(1, 0).Formula = "=""*""&" & ar.Address(0, 0) & "&""*"""
  Next
End Sub

By the way, in your first macro you need to reference the sheet in Cells, you should always reference the sheet in any range of cells:

Sub AddBarcodeToPart()
  Dim c As Range, sh1 As Worksheet
  Set sh1 = Sheets("Pick List")
  For Each c In sh1.Range("A6", sh1.Cells(Rows.Count, "A").End(xlUp))
    If c.Value <> "" Then
      c.Offset(1, 0).Font.Name = "Free 3 of 9 Extended"
    End If
  Next
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Anfinsen]

Can you confirm that the cells you think are empty actually are, ie no spaces, etc.

As for your formula, try using double quotes...something like this: c.Offset(1, 0).Formula = "=" & ""*"" & c & ""*""He

I can confirm the cells are empty.

I tried using the "escape" quotes as you suggested for the formula, and i get a "type mismatch" error.

 

[Anfinsen]

Hi @Anfinsen.
Thanks for posting on the forum.

I'm not quite sure what formula you want to put, but I'm assuming it's something like this:

Sub ValueForBarcode()
  Dim i As Long, sh1 As Worksheet
  Set sh1 = Sheets("Pick List")
  For i = sh1.Range("A" & Rows.Count).End(3).Row To 6 Step -1
    With sh1.Range("A" & i)
      If .Value <> "" Then
        .Offset(1, 0).Formula = "=""*""&" & .Address(0, 0) & "&""*"""
      End If
    End With
  Next
End Sub

If indeed after each cell with a value there is a space, then this can also be the case, the following code is smaller and works faster.

Sub ValueForBarcode_v2()
  Dim ar As Range, sh1 As Worksheet
  Set sh1 = Sheets("Pick List")
  For Each ar In sh1.Range("A6", sh1.Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    ar.Offset(1, 0).Formula = "=""*""&" & ar.Address(0, 0) & "&""*"""
  Next
End Sub

By the way, in your first macro you need to reference the sheet in Cells, you should always reference the sheet in any range of cells:

Sub AddBarcodeToPart()
  Dim c As Range, sh1 As Worksheet
  Set sh1 = Sheets("Pick List")
  For Each c In sh1.Range("A6", sh1.Cells(Rows.Count, "A").End(xlUp))
    If c.Value <> "" Then
      c.Offset(1, 0).Font.Name = "Free 3 of 9 Extended"
    End If
  Next
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Dante,
Thank you very much for reviewing and your detailed explanations. It worked perfectly and did exactly what I was attempting to do. Genuinely appreciated. Hope you have a great day!