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

**tvxl** · 03-05-2018, 12:08 PM

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:

Please Login or Register  to view this content.

Any help would be greatly appreciated!

-tvxl

**xladept** · 03-05-2018, 12:11 PM

Maybe:

Please Login or Register  to view this content.

**Norie** · 03-05-2018, 12:18 PM

Try this.

Please Login or Register  to view this content.

**tvxl** · 03-05-2018, 12:32 PM

Thanks for your response!

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**xlnitwit** · 03-05-2018, 12:34 PM

Hi,

You need a Set statement with object variables like Ranges

Please Login or Register  to view this content.

**tvxl** · 03-05-2018, 12:48 PM

Originally Posted by xlnitwit

Hi,

You need a Set statement with object variables like Ranges

Please Login or Register  to view this content.

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.

**xlnitwit** · 03-05-2018, 12:54 PM

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

Please Login or Register  to view this content.

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

**tvxl** · 03-05-2018, 12:58 PM

Originally Posted by xlnitwit

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

**xlnitwit** · 03-05-2018, 01:02 PM

I mean that invoiceNameValue should be declared as a Range too.

**tvxl** · 03-05-2018, 01:47 PM

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":

Please Login or Register  to view this content.

Any idea what that means?

**xladept** · 03-05-2018, 01:52 PM

Try this instead:

Please Login or Register  to view this content.

**tvxl** · 03-05-2018, 01:59 PM

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

**Marc L** · 03-05-2018, 02:06 PM

Hi !

Another way :

PHP Code:


Sub Demo1()

    With Sheet1.Range("B2", Sheet1.Cells(Rows.Count, 2).End(xlUp))

        .Value = Evaluate(Replace("IF(#>"""",""E-""&#,"""")", "#", .Address))

    End With

End Sub

As Sheet1 is a worksheet CodeName, safer than a worksheet name if renamed …

2016-02-22_195835.png

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

**xladept** · 03-05-2018, 02:14 PM

This works:

Please Login or Register  to view this content.

**Marc L** · 03-05-2018, 02:23 PM

It may not work if the active worksheet is not "Sage Import" 'cause of your inner Range("B2") without any worksheet before !

The reason why I prefer to use a CodeName like in my previous post …

**tvxl** · 03-05-2018, 03:29 PM

Originally Posted by xladept

This works:

Please Login or Register  to view this content.

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?

**Norie** · 03-05-2018, 03:53 PM

Try this.

Please Login or Register  to view this content.

**xladept** · 03-05-2018, 04:02 PM

The xlDown can be hinky that way - the last row in B is for the sheet in play but if the sheet in play is not the active sheet then it can vary wildly - better to use Norie's solution, which is the way I would have answered:

Please Login or Register  to view this content.

This code, like Norie's tells the program exactly where to get the row count

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Add a string between beginning and and of a chain per corresponding code

[SOLVED] How to delete the digits and the following spaces in the beginning of a string

Move Text From End Of String To Beginning

Excel 2007 : Add something to the beginning and end of the string

Add last word from a string to the beginning of new string

Add a string at the beginning and end

Is there away to add a zero to the beginning of number string?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions