Add TExt to a Cell With a Number

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
943
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try
Code:
For Each cl In Range("G7", Range("G" & Rows.Count).End(xlUp))
   cl.Value = "ABCD " & cl.Offset(, -1).Value
Next cl
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top