Linking Partial Cell Data

**swordswinger710** · 01-05-2009, 03:13 PM

Hello, I am looking for a macro for the following situation:

I have two cells on Sheet 1 - Cell A1 and Cell A2.

Cell A1 is the first cell that will receive either a number, or nothing. When it receives a number, the number will always contain five digits. (For example: '23456'.)

The contents of Cell A2 depend on the contents of Cell A1 - If Cell A1 contains a five digit number, then Cell A2 will display that same five-digit number with '-1' following it. (In our case: '23456-1'.) If Cell A1 contains no number, then Cell A2 will just be another ordinary cell allowing the user to enter whatever he/she desires.

The extension in Cell A2 should remain editable at all times - if the user wishes to change the '-1' to a '-2', for example, he/she should be able to do so without any error messages appearing. However, the user must not be able to change any of the first five digits in Cell A2 as long as Cell A1 contains data. A message should appear stating that changes to the first five digits should be performed in Cell A1 - which would then change Cell A2 accordingly.

Also, if the contents of Cell A1 are erased for any reason, Cell A2 should keep the five digit number, but lose whatever '-x' extension it contained.

Can this be done using Data Validation?

**jasoncw** · 01-05-2009, 04:01 PM

Here is one option that should handle what you are trying to accomplish. Right-click on the sheet tab, select View Code, then copy the following code:

Please Login or Register  to view this content.

HTH

Jason

**swordswinger710** · 01-05-2009, 04:47 PM

OK, that's not bad, however, when Cell A1 gets a 5-digit number, I need Cell A2 to display that number plus '-1'. So if Cell A1 gets '12345', then Cell A2 should read '12345-1'.

**jasoncw** · 01-05-2009, 04:57 PM

Easy enough. Just change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Jason

**swordswinger710** · 01-05-2009, 05:24 PM

Okay cool. Now I need to add your code to the one below without any error messages appearing; and the part of the code which checks Cell A1 for 5 digits should be removed in case I want to add a different amount.

Please Login or Register  to view this content.

Thanks a million!

**jasoncw** · 01-05-2009, 09:02 PM

Ok, try this and let me know how it works:

Please Login or Register  to view this content.

Jason

**swordswinger710** · 01-06-2009, 09:51 AM

Very cool! It's almost there - here's the bugs I found (my actual cell for Cell A1 is Cell B3:E3, and Cell A2 is Cell B8:E8 - they are merged):

1. When I enter a number into Cell B3:E3 everything goes good until I delete the number, and this error pops up - Run-time error '1004': Cannot change part of a merged cell.

2. When I have a number entered into Cell B3:E3 and Cell B8:E8 receives that number plus '-1', and I erase the number in Cell B8:E8, I get this error message - Run-time error '13': Type mismatch.

Here is what should happen -

1. When the number in Cell B3:E3 gets erased, Cell B8:E8 should lose it's '-x' extension with no messages appearing.

2. If Cell B3:E3 contains a number and the number in Cell B8:E8 is erased, the same message box we have appearing to prevent the numbers before the '-x' from being changed should appear now.

Let me know what you think I should do.

**jasoncw** · 01-06-2009, 10:33 AM

Ok, give this a shot:

Please Login or Register  to view this content.

Jason

**swordswinger710** · 01-06-2009, 11:44 AM

Thanks so much for everything so far . . . there's two more bugs that we need to solve yet.

1. Occasionally, when changing the number after the dash in Cell B8:E8, I'll get this message: Run-time error '6': Overflow. Not sure why.

2. If my number in Cell B3:E3 is more or less than five digits, I am unable to change the number after the dash in Cell B8:E8.

Are these fixable?

**jasoncw** · 01-06-2009, 01:07 PM

2. If my number in Cell B3:E3 is more or less than five digits, I am unable to change the number after the dash in Cell B8:E8.

Well, in your original post, you stated:

Cell A1 is the first cell that will receive either a number, or nothing. When it receives a number, the number will always contain five digits.

That is why I wrote the code in that manner. See if this works:

Please Login or Register  to view this content.

**swordswinger710** · 01-06-2009, 02:32 PM

I'm sorry, I realize I did state the five digit thing, but future speculation brought me to realize that the number of digits could very well change, which is why five digits became irrelevant.

Your last code fixed the problem with less than five digits, but when I use over five digits and try to change the number after the dash, Error 6 still appears.

What do you recommend?

**jasoncw** · 01-06-2009, 02:45 PM

Hmmm. Try changing:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

**swordswinger710** · 01-06-2009, 03:12 PM

Awesome! Try as I might, I couldn't get any more error messages. Thanks a million for your help! -Juda

P.S. I'll be posting another thread shortly, which is quite similar to this one. If you're interested, you could check it out, but only if you want to.

**jasoncw** · 01-06-2009, 03:19 PM

Good deal. Glad I could help.

Jason

**swordswinger710** · 01-07-2009, 11:32 AM

Whoops, I found another bug - I can add as many numbers as I want immediately before the dash in Cell B8:E8 without any error messages appearing. The macro should catch this as well if possible. -Juda

**swordswinger710** · 01-07-2009, 12:17 PM

Awesome, the macro at this post fixed the bug:

http://www.excelforum.com/excel-prog...ml#post2022631

A thousand thanks for your time, Jason! -Juda

Linking Partial Cell Data

LinkBack

Thread Tools

Rate This Thread

Display

Linking Partial Cell Data

A Start

Close!

A Few Bugs

So Close...

Apologies

Done!

Discovered Bug

The Bug Is Dead

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions