Macro to replace IF statement that compares 2 cellls

[ge0rge]

In cell M32 there will either be an O or the cell will be blank. In cell T51 I had placed an IF statment like this:

=IF(M32="O","","X")

Now that works ok but there is a glitch on my part. If T51 is blank because of M32 containing an "O" then the user will also enter a number into T51. Because I was using an IF formula in the cell it gets overwritten because of the user needing to enter data in the cell because of M32 containing a O. How do I write a macro so that a similar formula is used but still able to enter data manually without overwritting the formula? Thank You.

[stevekirk]

hi george,

i think you need to explain what your result needs to be ?

steve

[ge0rge]

Well thats it right there. I am probably making it hard to understand. I'll try to explain it better. In one cell there will either be an "O" or there won't. The "O" represents a pipe and the cell I place it in is part of a diagram that shows where it goes.

In another cell I had an IF statement that said if the "O" is there then this cell will be blank. The reason it is blank is so that the user can type in a measurement manually. This didn't work simply because my IF statement would then be overwritten and not work next time around. If there is no "O" in the 1st cell then the IF statement I used would place an "X" in the second cell telling the user that no pipe goes in that location. Hope this helps. Thanks for looking.

[mudraker]

Try this Worksheet change macro

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Please Login or Register  to view this content.

[royUK]

Have a user input cell and amend the fomula so that it reads from there if 0, that way you don't need VBA which wo't work if the user doesn't enable macros.

[ge0rge]

I am unsure of what you mean... If I don't use a macro how will it be semi-automated?

[ge0rge]

Try this Worksheet change macro

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Please Login or Register  to view this content.

I tried this code and I still have been unsuccessful with the results. I have also been playing around with this code but still am not getting the results I need.

Please Login or Register  to view this content.

This was/is the code i am trying to figure out. Any suggestions? Cell AQ19 will either be blank or an have an O in it.
If it has an O in it then then AW38 will be blank but allow for a manual user input.
If AQ19 is blank then AW38 will simply have an X in it. Any suggestions?

[mudraker]

ge0rge

In your code AW38 will get an X if AQ19 value = ""

What happens when you change the value in AQ19 - is AW38 being changed at all?

[VBA Noob]

Cross posted here as no response

http://www.ozgrid.com/forum/showthread.php?t=82785

VBA Noob

[ge0rge]

Mudraker:

It does that just fine. The problem arises when I go back to AQ19 and delete the O in the cell. I get: Run-Time Error '13', Type Mismatch.
This cell may have a O in it one day and nothing the next. Perhaps I need to adjust my code for the change back to nothing? Thanks for your help.


VBA Noob:

Is it wrong to cross post in that other forum? Their rules state that if you must cross-post that you include a link to the other forum so that valuable resources are not spent on something already answered. In this case i will apply the needed code to my project and if it works I will gladly let inform the other forum via my original post. I would have done the same here with a link included. You may not think I have been trying to resolve this but I been working on it and other issues all day. Its a side project apart from my daily routine of things. Sorry if I broke a posting rule, i will read the rules again. Thanks for the help though, it is appreciated.

[mudraker]

ge0rge

I suggest you read this sites rules :-
A thread with the rules is available at the top of each forum or see link below for rules.
See our Rule 9
Cross posting to other sites without providing a link to the threads posted on other forums causing time & effort wasting for the people trying to assist you. Continuing to cross post without provinding links will get you banned from sites, Memebrs of others will refuse to assist you. Memebrs are often members of multiple boards & can be quick to point out cross posted threads.


I can see no reason why the macro below will cause a Run-Time Error '13' error

Which line of code is causing the problem

Please Login or Register  to view this content.

[broro183]

hi all,

I agree with Roy & think the attempted solution is more complex than it needs to be. I think colour coded conditional formatting would be a better approach (ie no macros, no concern about overwriting if statements & it will always update if calculation is set to automatic).

George,
Conditional formatting (CF) won't put an "X" in the cell but you could colour the cell so that it makes people realise that data entry is not welcome - try this...

Select cell AW38
press [alt + o + d], choose "formula is" from the dropdown, copy "=ISBLANK($AQ$19)" (don't include the apostrophes), select the Format-Patterns, choose a colour (eg black), click [ok] all the way out.

Test the CF by entering & deleting the value in cell AQ19. If the AW38 cell doesn't change colour, select it & check that Excel hasn't added apostrophes around your formula (sometimes Excel has a mind of its own!).

You can use colours or patterns for the formatting & you could change your formula & highlight the cell (eg green/yellow) to show that you do want an entry there by using something like "=not(ISBLANK($AQ$19))" as a second condition.

My use of "isblank" is a little more flexible than checking for an "O" because users can enter something else as a "pipe" but isblank does require "empty" cells to actually be empty & not just appear blank eg " ".


hth
Rob

[ge0rge]

The color idea , though nice, won't work as intended. This circle represents a strand in a stressing sequence for a prestressed concrete member. In the cell I already placed an autoshape that looks like a stand. When the user needs a strand placed in that given location the O encircles it which will take an X out of the lower measurement section where they then need to place the measure of elongation for that strand.

[ge0rge]

Mudraker:

Sorry for the cross-post, never intended to break rules. I will try to be more aware next time. This is the line of code highlighted after error:

Please Login or Register  to view this content.

[broro183]

hi George,

Thanks for the reply :-)

I think that CF could be used (esp if your original If function already works - ignoring the issue of overwriting the formula) & I can see if I can get it to work for you if you'd like - can you please post a zipped example of your workbook?

re your type mismatch error:
Is the "O" a letter O typed into the cell or a circular autoshape?
Does the cell contain an error (I'm grasping at straws here)?
Do you only have a single cell selected?
Can you find out more details of what the target is when the error occurs?
- what do you get if you: press [ctrl + g] in VBE (the immediate window), type "?target.value" + press [enter] to see a result & also type "?target.address" + [enter]?

It's unlikely, but does it help if you add this extra check in at the start of your code?

Please Login or Register  to view this content.

hth
Rob

[ge0rge]

I figured out why the "X" will not be replaced if the circle is not present. I am using a cell made up of a 2 cell merge. If they are not merged your code works great. I since found out that excel and merged cells do not get along so I redid my worksheet to get away from the merge. Thanks for your help.