Macro - Replace Number with Specific text with formatting

[Roksman]

Hi All,

I've tried to create somewhat of an interactive table look up. My aim is when I insert a value into a cell, I want the macro to find that value in a seperate sheet and override my initial entry, while bringing the cell formatting across. I hope that makes sense? I've attached an image as it may help.

So ideally I was hoping this can be configured to automatically update the cell upon entry, is that possible?

image.png

[Roksman]

Probably best I add an "after" shot of what I'm trying to achieve. See pic.

image-1.png

Ideally I'd like to be able to continue adding values to the list. I'm planning to have over 400 entries.

So once I input the numeric value, hit enter, it retrieves the value instantaneously once I move onto the next row.

Appreciate any help. Thanks

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[Roksman]

Workbook attached.

I've removed any sensitive data and included multiple tables similar to how intend to use this functionality.

[protonLeah]

Please Login or Register  to view this content.

[Roksman]

Thanks Proton,

This is fantastic and exactly what I was looking for!

However I did notice something, when entering value "1", it actually returns item 10? I've tried to look at the code but cant find anything (very complex for me).

Also, would it be hard for me to add extra tables? I thought I could add to the "header_1" list, dont think that's the right way though?

Appreciate your efforts!

[protonLeah]

Change: Set MatchVal = ItemNumbers.Find(Target.Value)
To: Set MatchVal = ItemNumbers.Find(Target.Value, after:=ItemNumbers.End(xlDown))

---

For point #2, I'm sorry but I forgot to mention that header_1, ... etc. are named ranges. Select each additional table and click Define Name on the Formula Menubar. When header 7 table is selected, Excel will then name that block of cells header_7. You can then add it to the code as Range("header_7"). Note that I put them in two columns just for easier reading. Each Range("header_x") is separated by commas.

[Roksman]

Wow, a tad embarrassing, was right in front of me.

My other question is, how do I increase the table range? My sample file had 6 tables (headers) from column C to G, but I cant see anything that represents those columns? I'm currently trying to increase to 16 'Headers" - (8 on top, 8 on bottom as the sample format, instead of 3 and 3).

My assumption is that the below controls that, but I unfortunately cannot figure this out.

If Not Intersect(Target, _
Union( _
Range("header_1"), Range("header_2"), _
Range("header_3"), Range("header_4"), _
Range("header_5"), Range("header_6")) _
) Is Nothing Then

I apologise if I should have just sent the intended range in the sample file.

[protonLeah]

The original line is:

Please Login or Register  to view this content.

but it's too long to fit on the screen in the VB editor without having to scroll. So I used the "_" character break it into 5 shorter lines and wrap it to the next line down. You can delete the "_" at the end of each line and bring them all up to one line. The "_" at the end is a wordwrap code, it is not the same as the one here: "header_4".

Just delete the "_" at the end of the line and continue to press delete until the following line moves up. (You could also just copy the line at the top of this post and paste it in. Then to add your new tables it will look like:

Please Login or Register  to view this content.

[Roksman]

Thanks,

I understand what you mean in regards to the word wrap. However, if I'm copying the data from Columns B to G, and pasting them in H to M (effectively just doubling my table area), adding header_6 to header_12 to the wordwrap isn't going to work is it?

[Roksman]

Just to clarify, the code obviously works on cells

C2 to C23
E2 to E23
G2 to G23

And

C27 to C48
E27 to E48
G27 to G48

Adding the headers to the word wrap alone won't enable that same behaviour to other columns will it? Do I have to identify which columns I intend to add? And which cells for that matter?

I copied the existing Sample file from (B to G) and pasted in H to M, but simply adding the header numbers doesn't apply the logic to H2 to H23 etc

[Roksman]

Hi,

Problem solved, I worked out how to extend the Range. Thank you very much for all your help. Much appreciated

[Roksman]

Hi,

I've been trying to enhance this script by including a duplicate validation alert (msg box pop up) but having difficulty adding it into the ranges.
Appreciate any help.