change cell formula based on first 2 numbers of another

[realniceguy5000]

Hi,

Would anyone have a script for something like this?

I'm looking at range c5:c1200

Starting at C5

If the first 2 digits in that number start with 01 then I want the formula to be in G5 = F5*12
if the first 2 digits are 03 then I want G5 = F5*24
I have several of these if statement to put in However I need it to check the entire range as well because the formula may be different in each cell (G)?

Any Idea's to get me started.

Thank You, Mike

[VBA Noob]

I assume the cells are formatted as text to keep the leading zero instead of an '

You could use a custom function like

Please Login or Register  to view this content.

and this formula in G5 and drag down

=realniceguy(C5,F5)

VBA Noob

[realniceguy5000]

I assume the cells are formatted as text to keep the leading zero instead of an '

You could use a custom function like

Please Login or Register  to view this content.

and this formula in G5 and drag down


VBA Noob

Thank You for your input,

However I am getting a #name? error in the cell I place this code?

Please Login or Register  to view this content.

I first placed this code at the top in the sheet mod however since I have a bunch of other code in there I get an error in one of my other scripts that the "Varible not defined" I guess it has something to do with the Option Explicit part? If I put at the bottom it says something about only commets may appear after end sub

I really cant place in a mod or thisworkbook because I will be making a mass amount of copies and the mod's and this workbook dont transfer over to the copy?

A little more help?

Thank You, Mike

[VBA Noob]

Ideally you should declare all variables and Option Explicit as you've found out helps to do that. Remove it and you should be alright

You should also be able to tweak if you wish to avoid using a function

VBA Noob

[realniceguy5000]

Ideally you should declare all variables and Option Explicit as you've found out helps to do that. Remove it and you should be alright

You should also be able to tweak if you wish to avoid using a function

VBA Noob

Ok, I will work on this. Thanks again for your help...

Mike

[realniceguy5000]

Would have posted this sooner but couldn't get into site again. I was to late already got a ticket.

Thanks Mike


http://www.mrexcel.com/forum/showthread.php?t=385187

[shg]

i was too late already got a ticket.

[realniceguy5000]

I tried to make some changes but this is not working as expected, Maybe someone can point out why if columns C,D,E,F have nothing in them why wont the formula delete that is in G ?

Please Advise: Thank You, Mike

Please Login or Register  to view this content.

[martindwilson]

works for me!
filled c5:g25 with stuff
deleted c5:f25
ran macro g5:g25 cleared

[realniceguy5000]

works for me!
filled c5:g25 with stuff
deleted c5:f25
ran macro g5:g25 cleared

I have this in a worksheet change event proceedure. So if you fill:
c5 0122255500
d5 100
e5 50
f5 20

will say c6:f25 and blank the only formula that should show up is in G5 otherwise all the G's should be empty.

Make any sense? Thank You, Mike

[shg]

Maybe like this (untested):

Please Login or Register  to view this content.

[realniceguy5000]

Maybe like this (untested):

Please Login or Register  to view this content.

Thanks Shg,

It seems to be stuck in a loop of some kind? I get an hour glass and it stays there for a long time. I placed this in a worksheet change event mod on the sheet it is to run on.

What should I try?

[shg]

If you call it from a change event, you need to disable events before changing the worksheet, and re-enable events when you're done.

[realniceguy5000]

If you call it from a change event, you need to disable events before changing the worksheet, and re-enable events when you're done.

I was unable to get your changes to work correctly even after your last reply. so I modifed a bit. This below is so close but the problem is that the same formula is being copied to the next row down.

Where this is correct for

G5

Please Login or Register  to view this content.

But G6 should be this but it stays the same as the above?

Please Login or Register  to view this content.

Any More Clues?

Please Login or Register  to view this content.

On another note:

I really would like to put in case statments like vbnoob said above for COLUMN C for the 01 and 03 because I have so many more to do but I suppose I can just keep adding to this formula over and over again?

Anyway if you can please take another look and let me know what you see,I wont bug you anymore today.

Thank You, Mike

[shg]

Try this:

Please Login or Register  to view this content.

[realniceguy5000]

Try this:

Please Login or Register  to view this content.

Outstanding Shg... Thanks So much for your hard work...Seems to be working fine now!!!!

Now I can edit the formula to include more cases of the first 2 digits...

Thanks Again Shg...Your the best!!!!!

Mike

[shg]

You're welcome, thanks for the feedback.