VB to enter value automatically

[ntruong]

Hello Everyone,

I have a simple VB question (well, not so simple for myself)
This is what I'd like to do:
If A1 equals "J" then
B1 = 1.65 and B2 = 1.5
If A1 equals to some other letter (N, or E, etc...)
B1 and B2 should be blank

I'd like to use the same code for all 3 worksheets (with the same cell addresses).

Have a great weekend,
Nee

 

[DRJ]

sub test()

if sheets("Sheets1").range("A1").value = "J" then
sheets("Sheets1").range("B1").value = 1.65
sheets("Sheets1").range("B2").value = 1.5
else
sheets("Sheets1").range("B1").clearcontents
sheets("Sheets1").range("B2").clearcontents
end if

end sub

 

[Yogi Anand]

Hi Nee:

I hope I have understood you correctly ... try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [A1]) Is Nothing Then Exit Sub
    If Target = "J" Then
        [B1] = 1.65
        [B2] = 1.5
        ElseIf Target = "N" Or Target = "E" Then
        [B1] = ""
        [B2] = ""
    End If
End Sub

 

[HalfAce]

Hi Yogi,
Two questions for you.
(1) I notice you used (ByVal Target As Excel.Range) instead of just "As Range" What is the advantage of declaring it as an Excel range?

(2) Your use of the line If Intersect(Target, [A1]) Is Nothing Then Exit Sub, It appears the Intersect(Target, [A1]) part is what defines the target as [A1] (right?)... What is the advantage of using that as opposed to using
"If Target.Column = 1 And Target.Row = 1 Then"...?

Thanks (as usual),
Dan

 

[ntruong]

Hello Jacob / Yogi,

First off, thank you both for the posting back and for the codes.

I learned to use both sets of codes and I don't know what I missed here on Jacob's ... I pasted them to my ws but I could not get the function to work ..

Yogi -- Thanks; you understand exactly my question and the codes work nicely. However, I forgot to mention in my question that:
1. I'd like the target "J" to be "not" case sensitive and,
2. Is there any way to modify the codes so that when A1 is not equal to "J" then B1 and B2 should be blank. This way, I do not have to restrict myself to "N" or "E". In other words, here is my revised goal:
If A1 is equal to "J" (J should not be case sensitive)
B1 will be equal to 1.65 and B2 will be equal to 1.5
If A1 is not equal to "J"
B1 and B2 will be blank.

Again, thank you both, and I look forward to hearing from you.
Regards,
Nee

 

[HalfAce]

Hi ntruong,
I don't see either of the other guys now, so I'll give it a go.
Try changing Yogi's code to this and see if I understand what you're looking for.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Intersect(Target, [A1]) Is Nothing Then Exit Sub 
    If Target = "J" Or Target = "j" Then 
        [B1] = 1.65 
        [B2] = 1.5 
        Else 
        [B1] = "" 
        [B2] = "" 
    End If 
End Sub

This help?
Dan

 

[Yogi Anand]

Hi Dan:
Hi nee:

Dan's suggested change ...

If Target = "J" Or Target = "j" Then 
....

will work fine -- however, we can also use instead ...

If UCase(Target)= "J" Then
....

 

[HalfAce]

Yogi, I like it!
Now that is slick.
You are the man.

 

[DRJ]

To make text non case sensitive put

option compare text

at the top of your code. Like this

Option compare text

Sub Test
code here


This will make A=a B=b etc.

 

[ntruong]

Wow, HalfAce/Yogi/Jacob, I'm impressed!

I used HalfAce's suggested change (from Yogi's codes) together with Yogi's "Ucase" statement and everything works beautifully. Jacob, your statement works too!

Again, thank you all for taking your precious weekend time to help me.
Let's go for a beer ... some time.. of course my treat

Very appreciative,
Nee