Hi there,

I am working on a VBa that performs a number of functions.

The point of the VBa is to save worksheet1 as worksheet2, and then clear certain fields of content in worksheet1

Worksheet then becomes clear of data entered for that period ready to take date for the next period.

One of the cells in worksheet1 reads 'TonyGibb 2013' which is the name given worksheet2What I require to do is automatically change 'TonyGibb 2013' to 'TonyGibb 2014'

This cell location is "D5"

Any ideas how to go about this?Help would be much appreciated

✔ Best Answer

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 213into 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 213in D5, this formula will returnTonyGibb 214=LEFT(D5,FIND(" ",D5))&MID(D5,FIND(" ",D5)+1,256)+1

LEFT(D5,FIND(" ",D5)) will return

TonyGibbincluding the spaceMID(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])

you use:

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:

Sub IncrementTextNumber() Range("D5") = Left(Range("D5"), InStr(Range("D5"), " ")) & _ Mid(Range("D5"), InStr(Range("D5"), " ") + 1, 256) + 1 End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

That's it? Change 2013 to 2014? One time? Is there something you are not telling us?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes, sorry about that

Sheet1 is the working sheet

Sheet 2 will be named with what is in Sheet1!D5, that is 'TonyGibb 213'

Sheet1 data is copied onto Sheet2, number format only, no formulae

Sheet1 is then cleared of all input data, formulae remain

Sheet1!D5 then needs to change to 'TonyGibb 214'

Data then input into Sheet1

When completed, Sheet3 will be named with what is in Sheet1!D5, that is 'TonyGibb 214'

Sheet1 data copied onto Sheet3, number format only, no formulae

Sheet1 is then cleared of all input data, formulae remain

Sheet1!D5 then needs to change to 'TonyGibb 215'

Data then input into Sheet1

and so onHope that is a bit clearer. The VBA does numerous other functions to Sheet1 after the copy and paste procedure. What I am trying to do is automatically change the content of Sheet1!D5 so that user error is minimised

Teaching you how to fish... How would you accomplish this if you were doing it in Excel?

What if you had TonyGibb 213 in D5 and you wanted TonyGibb 214 in E5?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi,

Can not get it to work unless I split 'TonyGibb 213' into two cells which would be the easier way. Otherwise I can't get it.On the solution to the question posted, I have had to manage this another way to post certain dates that are entered as well. So problem sorted in one respect.

Will keep trying your response to see if I can get the answer I was looking for

message edited by tonygibb

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 213into 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 213in D5, this formula will returnTonyGibb 214=LEFT(D5,FIND(" ",D5))&MID(D5,FIND(" ",D5)+1,256)+1

LEFT(D5,FIND(" ",D5)) will return

TonyGibbincluding the spaceMID(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])

you use:

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:

Sub IncrementTextNumber() Range("D5") = Left(Range("D5"), InStr(Range("D5"), " ")) & _ Mid(Range("D5"), InStr(Range("D5"), " ") + 1, 256) + 1 End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

WOW smartypants, I would not have got that. Thanks very much for the heads up, I will definitely be able to use that in the future

Many thanks again

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History