VB to enter value automatically

ntruong

Active Member
Joined
Aug 4, 2003
Messages
261
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Nee:

I hope I have understood you correctly ... try
Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Active Member
Joined
Aug 4, 2003
Messages
261

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi Dan:
Hi nee:

Dan's suggested change ...
Code:
If Target = "J" Or Target = "j" Then 
....
will work fine -- however, we can also use instead ...
Code:
If UCase(Target)= "J" Then
....
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
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

Active Member
Joined
Aug 4, 2003
Messages
261
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 (y) :biggrin:

Very appreciative,
Nee
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,091
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top