# formula to text help

#### orsm6

##### Active Member
hi all - not sure how to tackle this, i keep thinking a formula inserted into a cell via macro but converts to text but not sure......

On sheet named Register in Column A is a number series. We use this number to assign a unique number to a change made.
Currently I have to drag the numbers down to fill.... but we now need to somehow set this up to reflect the current year.

is there a way by macro, to find the next empty cell in column A and input the value PCR21001?
then when a new change is entered, the macro finds next empty cell again and enters PCR21002 and so on.

how do i then stop or change the cell so that when we go to the year 2022 that all of the numbers already in the sheet stay as PCR21xxx
cell A1 can be used as a date reference if needed.

hope this makes sense and TIA for any help

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### sykes

##### Well-known Member
This is tested, only for this year. However, it should automatically use the current year for you, as requested.
Suggest that you test on a COPY of your workbook, until it's working as desired.

As you said that A1 was available, I've used it as a placeholder for your serial numbers.
If you custom format A1 on sheet "Register" as "000" then insert the current ref (033 eg), it'll be incremented by 1, each time the code fires.
Place this code into a normal code module:
VBA Code:
``````Sub change_ref()

With Sheets("Register")
Dim rw As Integer

rw = .Range("A65536").End(xlUp).Row + 1
.Range("A" & rw).Value = "PCR" & Format(Date, "YY") & .Range("A1").Text
.Range("A1").Value = .Range("A1").Value + 1

End With

End Sub``````
...then you just need to decide on how you want to trigger the code. I personally like something like an icon placed into the quick access toolbar, then each time a user wants to place a new change in the sheet, they just press the button, and the next col A cell will be ready.

Let us know if you need any help etc.

#### orsm6

##### Active Member
This is tested, only for this year. However, it should automatically use the current year for you, as requested.
Suggest that you test on a COPY of your workbook, until it's working as desired.

As you said that A1 was available, I've used it as a placeholder for your serial numbers.
If you custom format A1 on sheet "Register" as "000" then insert the current ref (033 eg), it'll be incremented by 1, each time the code fires.
Place this code into a normal code module:
VBA Code:
``````Sub change_ref()

With Sheets("Register")
Dim rw As Integer

rw = .Range("A65536").End(xlUp).Row + 1
.Range("A" & rw).Value = "PCR" & Format(Date, "YY") & .Range("A1").Text
.Range("A1").Value = .Range("A1").Value + 1

End With

End Sub``````
...then you just need to decide on how you want to trigger the code. I personally like something like an icon placed into the quick access toolbar, then each time a user wants to place a new change in the sheet, they just press the button, and the next col A cell will be ready.

Let us know if you need any help etc.
Thanks for the code Sykes.... i'll see if my system will allow me to change the date and have a play

#### orsm6

##### Active Member
This is tested, only for this year. However, it should automatically use the current year for you, as requested.
Suggest that you test on a COPY of your workbook, until it's working as desired.

As you said that A1 was available, I've used it as a placeholder for your serial numbers.
If you custom format A1 on sheet "Register" as "000" then insert the current ref (033 eg), it'll be incremented by 1, each time the code fires.
Place this code into a normal code module:
VBA Code:
``````Sub change_ref()

With Sheets("Register")
Dim rw As Integer

rw = .Range("A65536").End(xlUp).Row + 1
.Range("A" & rw).Value = "PCR" & Format(Date, "YY") & .Range("A1").Text
.Range("A1").Value = .Range("A1").Value + 1

End With

End Sub``````
...then you just need to decide on how you want to trigger the code. I personally like something like an icon placed into the quick access toolbar, then each time a user wants to place a new change in the sheet, they just press the button, and the next col A cell will be ready.

Let us know if you need any help etc.
Have tested this and seems to do exactly as i need it to. will monitor when the year changes.

thanks for your help with this.

#### sykes

##### Well-known Member
That's good news.
You're welcome, and thanks for the feedback.
Your accurate description of both the extant situation, and of your desired result, meant that there was no wasted time playing Mr. Excel ping-pong (drives me bonkers, that)!

Just one other thing - I'd said to place the CURRENT ref into A1, but of course, it needed to be the NEXT required ref. Guess you already worked that one out, though...

#### orsm6

##### Active Member
That's good news.
You're welcome, and thanks for the feedback.
Your accurate description of both the extant situation, and of your desired result, meant that there was no wasted time playing Mr. Excel ping-pong (drives me bonkers, that)!

Just one other thing - I'd said to place the CURRENT ref into A1, but of course, it needed to be the NEXT required ref. Guess you already worked that one out, though...
Yep, I sussed it out.

Replies
1
Views
98
Replies
5
Views
118
Replies
21
Views
281
Replies
5
Views
135
Replies
12
Views
707

1,127,100
Messages
5,622,717
Members
415,922
Latest member
gemmatay88

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back