formula to text help

orsm6

Active Member
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

orsm6

Active Member
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top