Need help with simple VB codes

[Nee]

Hello,

I am new to VB and I need help with coding this simple task:

Cell O6 is equal to "options" by default. Then I would want:
If cell Q7 equals to "disti"
cell O6 should be blank
otherwise, O6 should still = "options"

Please help,
Thanks,

Ne

 

[Rob van Gelder]

Worksheet function:
In cell O6:
=IF(Q7="disti","","options")

VB code:
Range("O6").Value = IIf(Range("Q7").Value = "disti", "", "options")

 

[Nee]

Hi Rob,

Thanks for the quick writeback. And,
First, I cannot use the ws function (I know some)
Second, there is special formatting in cell O6, and so I'd like o
remains unchanged (value and format) if Q7 is not equal to "disti", an
"disti" should be non-sensitive when coded.

Lastly, I'd like to use the "same" set of codes for 3 differen
worksheets (that have the same scenario) in my workbook, if the code
work.

Thanks,
Ne

 

[Rob van Gelder]

This should probably do the trick:

Sub test()
Dim wks As Worksheet

For Each wks In Worksheets(Array("Sheet1", "Sheet2"))
If LCase(wks.Range("Q7").Value) <> "disti" Then
wks.Range("O6").Value = "options"
End If
Next
End Sub

 

[Nee]

Hi Rob,

Thanks again.
I would like however to make this a Worksheet_change module tha
executes by itself as compared to a regular macro that requires user t
run it or click a button.

Second, I was not so clear about what I want in cell O6 (sorry). What
mean is, cell O6 by default has some text there with mixed format (som
are bold and underlined, some are not).
And so, if people type in "disti" in Q7,
O6 should be blank
Else, if Q7 is blank, O6 should remains unchanged, meaning it shoul
still be what it has there by default (texts with mixed format).
Because O6 has texts with mixed format, it prevents me from doing
common "Conditional formatting" .

Thanks for your time looking at this.
Ne

 

[Rob van Gelder]

I could give you an answer, but I don't think you know what the question is.

Would you please tell me which is correct?

Your 1st post:
If Q7 = "disti" then
O6 = blank
else
do nothing
end if


2nd post:
If Q7 = "disti" then
O6 = blank
elsif Q7 <> "disti" then
do nothing
else
???
end if


3rd post:
If Q7 = "disti"
O6 = blank
elsif Q7 = blank then
do nothing
else
???
end if

 

[Nee]

Hi Rob,

Thank you for your patience and your help so far. I appreciate it.
I think the 2nd post is what I'd like to do (with a minor change marke
with a *):

2nd post:
If Q7 = "disti" then
O6 = blank
elsif Q7 <> "disti" then
* O6 should be O6 as defaulted (with text & format) *
end if

Please I'd like to make sure:
IF Q7 is not equal to "disti",
O6 should remains unchanged in regards to the text and the format o
the text in O6.
Because people can always type in "disti" in Q7, then change their min
and delete "disti" from Q7... then in this case O6 should be able t
"toggle" -- it can be blank or display what is there before based o
the change in Q7.

Many thanks for looking at my question.

Best regards,
Ne

 

[Tom Ogilvy]

In O6 put in the formula

=if(Q7="disti","","Options")

no need to use a macro.

 

[Rob van Gelder]

Let's take a scenario:

O6 = "hello world"
Q7 = "something"

So if a user change Q7 to "disti" then later changes it "something" then it
should still = "hello world" ?

That's not possible without saving the original value somewhere for safe
keeeping.

Try this instead:
O7 = "hello world"
O6 = =if(Q7="disti","",O7)

 

[Nee]

Hello Rob,
Good to see your postback. I understand this is getting confused.. pl
see my replies (start in CAPS, for readibility reason) to you
questions below:
==========================================
Let's take a scenario:

O6 = "hello world"
(OK, EXCEPT THAT I HAVE "h" and "w" bolded, in this scenario)
Q7 = "something"

So if a user change Q7 to "disti" then later changes it "something
then it
should still = "hello world" ?
(NEE: YES, EXCEPT THAT "HELLO WORLD" SHOULD BE ABLE TO KEEP IT
ORIGINAL "MIX" FORMAT OF HAVING THE "H" AND THE "W" BOLDED -- this i
where the confusion lies, Otherwise, a straight conditional formattin
would suffice)

That's not possible without saving the original value somewhere fo
safe
keeeping.

Try this instead:
O7 = "hello world"
O6 = =if(Q7="disti","",O7)
(NEE: THIS DOES NOT WORK FOR MY CASE.
1) I need to leave O7 blank
2) O6 should display "hello world" by default
3) Even if I can make O7 = "hello world", how does O6 pick up O7 an
also picks up its format (I mean the bolded "h" and "w")

I have a workaround but I'm now become just curious to see if thi
challenge can be beat.
In any case, I appreciate your time Rob and attention in my question
It is very nice of you.
Later,
Ne

 

[Rob van Gelder]

Sorry, I don't have a solution for your other than what I've already
suggested.