VBA - Can I?

  • Thread starter Thread starter robert morris
  • Start date Start date
R

robert morris

Can I use VBA code to enter a number say 1 or 2 or 3 or 4 in Col F9 and have
it multiply by a static number, say 2 and have the result display in the same
Col F9?

Bob
 
You can, but wouldn't it be safer to use a formula in (say) an adjacent cell:

=F9*2

But if you want to try a macro...

Rightclick on the worksheet tab that should have this behavior. Select View
code. Paste this into the newly opened code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Me.Range("F7")
If Intersect(Target, .Cells) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value * 2
Application.EnableEvents = True
End If
End With
End Sub

Then back to excel to test it.
 
hi
not sure if i understand but...
try this.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = [F9]
Target.Value = Target.Value * 2
End Sub

worksheet code. right click the sheet tab and from the popup, click "view
code"
Paste the above into the code window. works only on the sheet you paste it
into.

Regards
FSt1
 
Thanks for the reply but the code doesn't change the number entered in Cell
F9. If I type in "3" in F9 and "enter" the Cell F9 should show the result of
"6". It shows 3.

I forgot to say the Range should be Column F9:F188.

I am presently using an adjacent cell with a formula which works but a code
would make life easier.

Bob

FSt1 said:
hi
not sure if i understand but...
try this.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = [F9]
Target.Value = Target.Value * 2
End Sub

worksheet code. right click the sheet tab and from the popup, click "view
code"
Paste the above into the code window. works only on the sheet you paste it
into.

Regards
FSt1

robert morris said:
Can I use VBA code to enter a number say 1 or 2 or 3 or 4 in Col F9 and have
it multiply by a static number, say 2 and have the result display in the same
Col F9?

Bob
Click to expand...
Click to expand...
 
Or use this.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("F9:F188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value * 2
Application.EnableEvents = True
End If
End With
Next myCell
End Sub

ps. It's usually better to give an accurate description for what you need.
It'll save time on your part and the part of responders.
 
Dave,

Column F9:F188 is perfect. Now if I go to Column G9:G188 times the static
number of 5, using the same code I get an Ambiguous error. I also need for
Range H9:H188 *10 and Range I9:I188 * 20. Must I change the name"Private
Sub Worksheet_Change" for each one? Your help is greatly appreciated.

Bob



----- Original Message -----
From: "Dave Peterson" <[email protected]>
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: Re: VBA - Can I?

Or use this.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("F9:F188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value * 2
Application.EnableEvents = True
End If
End With
Next myCell
End Sub
Click to expand...


robert morris said:
Thanks for the reply but the code doesn't change the number entered in Cell
F9. If I type in "3" in F9 and "enter" the Cell F9 should show the result of
"6". It shows 3.

I forgot to say the Range should be Column F9:F188.

I am presently using an adjacent cell with a formula which works but a code
would make life easier.

Bob

FSt1 said:
hi
not sure if i understand but...
try this.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = [F9]
Target.Value = Target.Value * 2
End Sub

worksheet code. right click the sheet tab and from the popup, click "view
code"
Paste the above into the code window. works only on the sheet you paste it
into.

Regards
FSt1

robert morris said:
Can I use VBA code to enter a number say 1 or 2 or 3 or 4 in Col F9 and have
it multiply by a static number, say 2 and have the result display in the same
Col F9?

Bob
Click to expand...
Click to expand...
Click to expand...
 
Again, it's better to give all the information in your original posts. It'll
save your time and the responder's time.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range
Dim myMultiplier As Double

Set myRngToCheck = Me.Range("F9:I188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Select Case myCell.Column
Case Is = Me.Range("F1").Column
myMultiplier = 2
Case Is = Me.Range("g1").Column
myMultiplier = 5
Case Is = Me.Range("h1").Column
myMultiplier = 10
Case Is = Me.Range("i1").Column
myMultiplier = 20
Case Else
myMultiplier = 0
End Select

If myMultiplier = 0 Then
'something bad happened!
Else
Application.EnableEvents = False
.Value = .Value * myMultiplier
Application.EnableEvents = True
End If
End If
End With
Next myCell
End Sub

robert said:
Dave,

Column F9:F188 is perfect. Now if I go to Column G9:G188 times the static
number of 5, using the same code I get an Ambiguous error. I also need for
Range H9:H188 *10 and Range I9:I188 * 20. Must I change the name"Private
Sub Worksheet_Change" for each one? Your help is greatly appreciated.

Bob

----- Original Message -----
From: "Dave Peterson" <[email protected]>
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: Re: VBA - Can I?
Or use this.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("F9:F188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value * 2
Application.EnableEvents = True
End If
End With
Next myCell
End Sub
Click to expand...

robert morris said:
Thanks for the reply but the code doesn't change the number entered in Cell
F9. If I type in "3" in F9 and "enter" the Cell F9 should show the result of
"6". It shows 3.

I forgot to say the Range should be Column F9:F188.

I am presently using an adjacent cell with a formula which works but a code
would make life easier.

Bob

FSt1 said:
hi
not sure if i understand but...
try this.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = [F9]
Target.Value = Target.Value * 2
End Sub

worksheet code. right click the sheet tab and from the popup, click "view
code"
Paste the above into the code window. works only on the sheet you paste it
into.

Regards
FSt1

:

Can I use VBA code to enter a number say 1 or 2 or 3 or 4 in Col F9 and have
it multiply by a static number, say 2 and have the result display in the same
Col F9?

Bob
Click to expand...
Click to expand...
Click to expand...
 
And since this line is within the "with mycell" structure, you can change this:

Select Case myCell.Column
to
Select Case .Column

(It'll look a little cleaner.)

Dave said:
Again, it's better to give all the information in your original posts. It'll
save your time and the responder's time.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range
Dim myMultiplier As Double

Set myRngToCheck = Me.Range("F9:I188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Select Case myCell.Column
Case Is = Me.Range("F1").Column
myMultiplier = 2
Case Is = Me.Range("g1").Column
myMultiplier = 5
Case Is = Me.Range("h1").Column
myMultiplier = 10
Case Is = Me.Range("i1").Column
myMultiplier = 20
Case Else
myMultiplier = 0
End Select

If myMultiplier = 0 Then
'something bad happened!
Else
Application.EnableEvents = False
.Value = .Value * myMultiplier
Application.EnableEvents = True
End If
End If
End With
Next myCell
End Sub

robert said:
Dave,

Column F9:F188 is perfect. Now if I go to Column G9:G188 times the static
number of 5, using the same code I get an Ambiguous error. I also need for
Range H9:H188 *10 and Range I9:I188 * 20. Must I change the name"Private
Sub Worksheet_Change" for each one? Your help is greatly appreciated.

Bob

----- Original Message -----
From: "Dave Peterson" <[email protected]>
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: Re: VBA - Can I?
Or use this.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("F9:F188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value * 2
Application.EnableEvents = True
End If
End With
Next myCell
End Sub
Click to expand...

robert morris said:
Thanks for the reply but the code doesn't change the number entered in Cell
F9. If I type in "3" in F9 and "enter" the Cell F9 should show the result of
"6". It shows 3.

I forgot to say the Range should be Column F9:F188.

I am presently using an adjacent cell with a formula which works but a code
would make life easier.

Bob

:

hi
not sure if i understand but...
try this.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = [F9]
Target.Value = Target.Value * 2
End Sub

worksheet code. right click the sheet tab and from the popup, click "view
code"
Paste the above into the code window. works only on the sheet you paste it
into.

Regards
FSt1

:

Can I use VBA code to enter a number say 1 or 2 or 3 or 4 in Col F9 and have
it multiply by a static number, say 2 and have the result display in the same
Col F9?

Bob
Click to expand...
Click to expand...
Click to expand...
 
Dave,

Beautiful, beautiful! Thank you so much and I will heed your advise.

Bob


robert morris said:
Dave,

Column F9:F188 is perfect. Now if I go to Column G9:G188 times the static
number of 5, using the same code I get an Ambiguous error. I also need for
Range H9:H188 *10 and Range I9:I188 * 20. Must I change the name"Private
Sub Worksheet_Change" for each one? Your help is greatly appreciated.

Bob



----- Original Message -----
From: "Dave Peterson" <[email protected]>
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: Re: VBA - Can I?

Or use this.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("F9:F188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value * 2
Application.EnableEvents = True
End If
End With
Next myCell
End Sub
Click to expand...


robert morris said:
Thanks for the reply but the code doesn't change the number entered in Cell
F9. If I type in "3" in F9 and "enter" the Cell F9 should show the result of
"6". It shows 3.

I forgot to say the Range should be Column F9:F188.

I am presently using an adjacent cell with a formula which works but a code
would make life easier.

Bob

FSt1 said:
hi
not sure if i understand but...
try this.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = [F9]
Target.Value = Target.Value * 2
End Sub

worksheet code. right click the sheet tab and from the popup, click "view
code"
Paste the above into the code window. works only on the sheet you paste it
into.

Regards
FSt1

:

Can I use VBA code to enter a number say 1 or 2 or 3 or 4 in Col F9 and have
it multiply by a static number, say 2 and have the result display in the same
Col F9?

Bob
Click to expand...
Click to expand...
Click to expand...
 
Back
Top