|First, the Excel solution, then the VBA version.|
re: "Can not get it to work unless I split 'TonyGibb 213' into two cells"
There is no need to split the entry into 2 cells. Of course, in Excel, the formula would need to be a different cell, but you don't need to split the original value into 2 cells. We can use a formula to split the TonyGibb 213 into a text value and a quasi-numerical value.
Since Excel likes working with numbers more than text, it will try to execute a mathematical operation on a "text number" if it can. Therefore, we should be able to add 1 to the text value 213.
For example, if you had TonyGibb 213 in D5, this formula will return TonyGibb 214
=LEFT(D5,FIND(" ",D5))&MID(D5,FIND(" ",D5)+1,256)+1
LEFT(D5,FIND(" ",D5)) will return TonyGibb including the space
MID(D5,FIND(" ",D5)+1,256) will return the text value 213, which we can add 1 to.
(The 256 is simply to allow for any number of digits at the end of the string. If you know it will always be 3, just use 3)
OK, so that works within Excel. Once we move into VBA, we have to be aware of 2 things:
1 - VBA doesn't need to use a separate cell for the return value. You can simply replace the existing value directly in the cell. You pull the existing value into VBA, modify it and put it back in the cell. There's no such thing as a circular reference in VBA. It can read the contents of a cell and overwrite it with a modifed version.
2 - VBA does not use the text function FIND. Instead, it uses INSTR. In addition, the arguments are reversed. Instead of:
FIND(find_text, within_text, [start_num])
InStr( [start], string, substring, [compare] )
where string = within_text and substring = find_text
So in VBA, you should be able to use something like this to increment the numerical portion of D5 each time the code is run:
Range("D5") = Left(Range("D5"), InStr(Range("D5"), " ")) & _
Mid(Range("D5"), InStr(Range("D5"), " ") + 1, 256) + 1
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03