VBA code to replace "1" with "7:00/ 15:30"

**GLV** · 03-24-2023, 08:16 AM

Hi, everyone,

I am an VBA newbie to say at most. Please help me! I have searched through dozens of post, but nothing worked for me. Or maybe i don't know what to search

I have an excel workbook with more than 2 or 3 sheets. It's an schedule for the place i work at. We are working in shifts: 1st is from 7 to 15:30, 2nd is from 13:30 to 22:00 and 3rd is from 22:00 to 06:30.
As mentioned in the title, i need some code (or any idea) that will automatically replace 1, 2 or 3 with the above hour intervals.
I want it to happen in real time. I type 1, press Enter and poof!, 7:00/ 15:30 appears instead of 1. And so on for 2 and 3.

Thank you all !

**TMS** · 03-24-2023, 08:24 AM

VBA is Macros. Seems you have a contradiction in your requirements.

**6StringJazzer** · 03-24-2023, 08:24 AM

Originally Posted by GLV

But with no macros.

I am a little confused here. You posted in VBA subforum asking for some code but then you say "no macros". This requires macros.

You would use the Worksheet_Change event sub in the module for the sheet containing this data. However, the way I would do this is by addressing the design of your data, and use formulas instead of VBA. Also, it is not good design to cram two pieces of data into one cell as text. You should have a start time in one cell and end time in another cell, using Excel time values.

Please attach a file and I can show you solutions that work both ways.

**Artik** · 03-24-2023, 08:56 AM

Below, all I can do for you without macros.
Apply custom cell formatting.

Please Login or Register  to view this content.

If you type 1 you get "7:00/15:30", if you type 2 you get "13:30/22:00". For any other positive numeric value you will get "22:00/6:00".

Artik

**GLV** · 03-24-2023, 09:41 AM

Originally Posted by 6StringJazzer

I am a little confused here. You posted in VBA subforum asking for some code but then you say "no macros". This requires macros.

You would use the Worksheet_Change event sub in the module for the sheet containing this data. However, the way I would do this is by addressing the design of your data, and use formulas instead of VBA. Also, it is not good design to cram two pieces of data into one cell as text. You should have a start time in one cell and end time in another cell, using Excel time values.

Please attach a file and I can show you solutions that work both ways.

I attached the example.

**6StringJazzer** · 03-24-2023, 10:21 AM

Here is a VBA solution and a redesign.

I assumed that 13:30 was an error in your description and should be 15:30.

**GLV** · 03-24-2023, 10:55 AM

The VBA example is almost perfect, with some mentions:
1. I dont need the message telling me it didnt find the shift. I tried to remove that If, but... no success. I messed everything up.
2. When i clear the content of the cell it automatically adds "/". Can you remove that also, please ?
3. I need to apply this code to a range, from C1 to AG400 (or to AG80, depending on needs). Otherwise, when i enter 1, 2, or 3 on the Crt. No on A column it also changes the content.

Many Thanks !

**6StringJazzer** · 03-24-2023, 11:39 AM

Since you were asking for VBA code, hopefully you can take the work I've done so far for free and tailor it to your specific needs.

**GLV** · 03-24-2023, 12:20 PM

Originally Posted by 6StringJazzer

Since you were asking for VBA code, hopefully you can take the work I've done so far for free and tailor it to your specific needs.

I really appreciate your help and didn't mean to be ungrateful. I only asked those modification because i practically know nothing about coding.
Thank you once again! I hope i will manage to fix it.

**GLV** · 03-25-2023, 07:03 AM

Originally Posted by GLV

I really appreciate your help and didn't mean to be ungrateful. I only asked those modification because i practically know nothing about coding.
Thank you once again! I hope i will manage to fix it.

My apologies!
This is the another post:
https://www.excelforum.com/excel-pro...ml#post5806823

VBA code to replace "1" with "7:00/ 15:30"

LinkBack

Thread Tools

Rate This Thread

Display

VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Re: VBA code to replace "1" with "7:00/ 15:30"

Thread Information

Users Browsing this Thread

Similar Threads

How to nest an "if", "substitute", "find and replace" commands

Budget assist: Entering "Weekly" "Fortnightly" "Monthly" OR "Yearly" with formulas

Replace "ActiveSheet.Pictures.Insert" with "Shapes.Addpicture" and "SaveWithDocument"

[SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"

[SOLVED] VBA help needed to remove all "/" then replace with "-" from cell "B3"and "B5"

Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"

[SOLVED] How to USE """"" cells count """"" change font color

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions