How to Add a String to the beginning of every value in a range

Hello Excel Forum!

I could use some help on an easy one. My goal is to identify all cells within a column that have values, then add the string "E-" to the beginning of each cell.

I know how to identify the the cells with values within my range and I know that I need to use & to concatenate my string with each cell.

I am not sure how to identify each value within the loop and then how to actually execute the concatenate.

Here is my first attempt:

Public Sub Concatenate Test
Dim invoiceNameRng As Range
Dim invoiceNameValue As Variant

invoiceNameRng = ThisWorkbook.Worksheets("Sheet 1").Range("B2", Range("B2").End(xlDown))

For Each invoiceNameValue In invoiceNameRng
    invoiceNameValue.Value = "E-" & invoiceNameValue
Next

Any help would be greatly appreciated!

-tvxl

Thanks for your response!

I tried the following and got the error "Object variable or With block variable not set" on the line

invoiceNameRng = ThisWorkbook.Worksheets("Sheet 1").Range("B2", Range("B2").End(xlDown))

Public Sub ConcatenateTest()

Dim invoiceNameRng As Range
Dim invoiceNameValue As Variant
    
invoiceNameRng = ThisWorkbook.Worksheets("Sheet 1").Range("B2", Range("B2").End(xlDown))

For Each invoiceNameValue In invoiceNameRng
If IsNumeric(invoiceNameValue.Value) Then _
    invoiceNameValue.Value = "E-" & invoiceNameValue
Next

End Sub

Originally Posted by xlnitwit

Hi,

You need a Set statement with object variables like Ranges

Set invoiceNameRng = ThisWorkbook.Worksheets("Sheet 1").Range("B2", Range("B2").End(xlDown))

Yes, Thank you!!

Quick question: why didn't we have to tell the sub what invoiceNameValue was? We just told it that we would change all invoiceNameValue's within the range, but we never specified what it was (beyond indicating it was a variant above). I don't quite understand how it know what were talking about.

Originally Posted by xlnitwit

Strictly speaking it would have been better to declare it as a Range. The code is equivalent to

For Each invoiceNameValue In invoiceNameRng.Cells

so it just loops through each cell in the specified range and amends it as required.

Didn't we declare it as a range at the top when we said:

Dim invoiceNameRng As Range

I put the contents of this sub within the contents of a much larger sub and the line below gives me the error "Application-defined or object-defined error":

Set invoiceNameRng = ThisWorkbook.Worksheets("Sage Import").Range("B2", Range("B2").End(xlDown))

Any idea what that means?

That gives me a compile error of "Expected: list separator or )"

Originally Posted by xladept

This works:

Set InvoiceNameRng = _
ThisWorkbook.Worksheets("Sage Import").Range("B2:B" & Range("B2").End(xlDown).Row)

Oddly enough it works within it's own sub. However, when I next it within the rest of my sub, it halts at row 19 on every page that I try it with. Any idea why that might be?