Add TExt to a Cell With a Number

[Guzzlr]

For Each cl In Range("G7", Range("G" & Rows.Count).End(xlUp))
If cl.Value <> Null Then
   cl.Value = "ABCD " & Range("F")
   End If
Next cl

Hello All,
I'm attempting to add the letters ABCD to each cell in column G, starting at G7, with the number in column F
Example:
F7=2567, then G7 would = ABCD 2567 (a space would be between ABCD & 2567)
Thanks for the help

 

[Fluff]

Try

For Each cl In Range("G7", Range("G" & Rows.Count).End(xlUp))
   cl.Value = "ABCD " & cl.Offset(, -1).Value
Next cl

 

[Rick Rothstein]

Try

For Each cl In Range("G7", Range("G" & Rows.Count).End(xlUp))
   cl.Value = "ABCD " & cl.Offset(, -1).Value
Next cl

Or, without the loop...

 With Range("G7", Cells(Rows.Count, "G").End(xlUp))
   .Value = Evaluate("""ABCD ""&" & .Offset(, -1).Address)
 End With

 

[Greg Truby]

In your initial post you're scanning for cells in G that are not blank, this make me think you might have cells in G that ARE blank where you don't want the "ABCD" entered into the cell. If that's the case then perhaps something like so:

Sub foo()

    Dim celItem     As Excel.Range, _
        rngTarg(2)  As Excel.Range
            
    On Error Resume Next

    Rem     The "23" in the second arg below is the sum of the constants
    Rem     to catch errors, logicals, numbers & text.
    Set rngTarg(0) = Range("G7", Cells(Rows.Count, "G").End(xlUp)).SpecialCells(xlCellTypeConstants, 23)
    Set rngTarg(1) = Range("G7", Cells(Rows.Count, "G").End(xlUp)).SpecialCells(xlCellTypeFormulas, 23)
    
    If rngTarg(0) Is Nothing Then
        Set rngTarg(2) = rngTarg(1)
    ElseIf rngTarg(1) Is Nothing Then
        Set rngTarg(2) = rngTarg(0)
    Else
        Set rngTarg(2) = Union(rngTarg(0), rngTarg(1))
    End If
    
    If rngTarg(2) Is Nothing Then Exit Sub
        
    On Error GoTo 0
    
    For Each celItem In rngTarg(2).Cells
        With celItem
            .Value = "ABCD " & .Offset(, -1).Value
        End With
    Next celItem

End Sub

 

[Guzzlr]

Or, without the loop...

 With Range("G7", Cells(Rows.Count, "G").End(xlUp))
   .Value = Evaluate("""ABCD ""&" & .Offset(, -1).Address)
 End With

This is an interesting concept. The problem is it started in G7, and went up to G1, instead of starting in G7, and going down to the last row of F.
Thanks for the help

 

[Rick Rothstein]

This is an interesting concept. The problem is it started in G7, and went up to G1, instead of starting in G7, and going down to the last row of F.

I was having trouble deciding on how your data was laid out and tried to use the interpretation other posters used to decide how I should code my proposal. I am still not entirely sure about your layout, but let me try... does this work correctly for you?

With Range("F7", Cells(Rows.Count, "F").End(xlUp))
  .Offset(, 1).Value = Evaluate("""ABCD ""&" & .Address)
End With

 

[Guzzlr]

With Range("F7", Cells(Rows.Count, "F").End(xlUp))
  .Offset(, 1).Value = Evaluate("""ABCD ""&" & .Address)
End With

Yes, this is working. it is adding ABCD to the numbers in Column G, row-by-row, beginning in G7, and stopping at G48...instead of beginning in G7, and moving up to the top of the page, with empty cells in column G below G7.
Thank you for your help