Add TExt to a Cell With a Number

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
759
Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,416
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
For Each cl In Range("G7", Range("G" & Rows.Count).End(xlUp))
   cl.Value = "ABCD " & cl.Offset(, -1).Value
Next cl
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,318
Office Version
  1. 2010
Platform
  1. Windows
Try
Code:
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...
Code:
 With Range("G7", Cells(Rows.Count, "G").End(xlUp))
   .Value = Evaluate("""ABCD ""&" & .Offset(, -1).Address)
 End With
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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:

Code:
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
 
Last edited:

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
759

ADVERTISEMENT

Or, without the loop...
Code:
 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,318
Office Version
  1. 2010
Platform
  1. Windows
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?
Code:
With Range("F7", Cells(Rows.Count, "F").End(xlUp))
  .Offset(, 1).Value = Evaluate("""ABCD ""&" & .Address)
End With
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
759
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,127
Messages
5,527,001
Members
409,733
Latest member
revender17

This Week's Hot Topics

Top