Macro Trouble

[JR]

Hello,

I have this macro: (below), but for some reason I can not get it to run. I
am very new to this and I am missing a step. I copy this to the macro editor
in Excel, but when I go back to the worksheet and try to run the macro from
the dropdown menu there is nothing listed as a name?? Any help would be
great. I think I am not doing the steps correctly to insert the macro into
my worksheet.

Thanks JR


(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

 

[JE McGimpsey]

FIrst, you left off the beginning of the macro - it's intended to be a
Worksheet_Change Event Macro:

Public Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

which should go in the worksheet code module (right-click the worksheet
tab and choose View Code).

Event macros won't show up in the macro list - they're intended to run
automatically whenever an "event" (such as the user making an entry, or
a calculation) occurs.

If you want a regular macro that you can invoke from the menu, or a
keyboard shortcut or button, try putting this in a regular code module:


Public Sub MultiplyB1byA1()
With Range("B1")
.Value = .Value * Range("A1").Value
End With
End Sub

 

[JR]

That worked perfect, thanks much. One last qestion. What if I want it to
work on a range of cells, such as A1 through A10, also with B1 through B10

Thanks Again,

JR

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A10")) Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value * .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

 

[JR]

Hello,

I tried this for the range, but when I enter a number into an "A" cell, the
"B" cell is zero?

Thanks JR

 

[JE McGimpsey]

I'd inadvertently changed your original "+" to "*" in my sample code.
Since you said it "worked perfect", I left it in. A * 0 = 0.

Change the * to +.

 

[JR]

Perfect.

Thank you foar all the help

JR

I'd inadvertently changed your original "+" to "*" in my sample code.
Since you said it "worked perfect", I left it in. A * 0 = 0.

Change the * to +.