Solved Change a Text Date entry via VBA

September 23, 2013 at 14:49:37
Specs: Windows 7
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 worksheet2

What 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


See More: Change a Text Date entry via VBA

Report •


✔ Best Answer
September 24, 2013 at 10:13:19
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])

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



#1
September 23, 2013 at 17:08:44
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.


Report •

#2
September 24, 2013 at 01:42:48
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 on

Hope 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


Report •

#3
September 24, 2013 at 05:27:56
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.


Report •

Related Solutions

#4
September 24, 2013 at 09:41:02
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


Report •

#5
September 24, 2013 at 10:13:19
✔ 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 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])

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


Report •

#6
September 26, 2013 at 06:59:11
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

Report •


Ask Question