VBA - Can I?
 

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

VBA - Can I?
 

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.

robert morris wrote:

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

--

Dave Peterson

VBA - Can I?
 

ps. I typed F7 instead of F9 in the code. Watch out for that typo.

Dave Peterson wrote:

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.

robert morris wrote:

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

--

Dave Peterson

--

Dave Peterson

VBA - Can I?
 

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" wrote:

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

VBA - Can I?
 

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" wrote:

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" wrote:

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

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

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 Peterson wrote:

ps. I typed F7 instead of F9 in the code. Watch out for that typo.

Dave Peterson wrote:

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.

robert morris wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

VBA - Can I?
 

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"
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: 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


"robert morris" wrote:

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" wrote:

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" wrote:

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

VBA - Can I?
 

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 morris wrote:

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"
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: 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

"robert morris" wrote:

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" wrote:

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" wrote:

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

--

Dave Peterson

VBA - Can I?
 

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 Peterson wrote:

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 morris wrote:

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"
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: 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

"robert morris" wrote:

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" wrote:

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" wrote:

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

--

Dave Peterson

--

Dave Peterson

VBA - Can I?
 

Dave,

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

Bob


"robert morris" wrote:

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"
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: 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


"robert morris" wrote:

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" wrote:

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" wrote:

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