Target.Address syntax

[Coolboy55]

Can someone tell me how to correct my syntax in the following code? Right now it does nothing. Thanks!!


Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long

l_LastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row

For x = 3 To (l_LastRow - 1) Step 1
If Target.Address = Cells(x, 4) Then
Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
End If
Next x

End Sub

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[agarwaldvk]

Dear Coolboy

For x = 3 To (l_LastRow - 1) Step 1
If Target.Address = Cells(x, 4) Then
Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
End If
Next x


In your above code, for starters,

Target.Address will return the absolute (by default) address of the Target cell. From memory, the argument 'external' by default is False giving you the cell address.

The 'Cells(x,4)' will return the value in the Cell(x,4). More than likely these 2 values will always be different making the if condition False. With you not having the Else clause, it doesn't do anything!

Hope this helps!


Best regards


Deepak Agarwal

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson

[Trevor Shuttleworth]

Maybe:

If Target.Address = Cells(x, 4).Address Then

Regards

Trevor


"Coolboy55" <[email protected]> wrote
in message news:[email protected]...
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile:
> http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926
>

[Dave Peterson]

One more way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim l_LastRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub

l_LastRow = Me.Cells.Find(What:="*", lookat:=xlWhole, _
LookIn:=xlFormulas, After:=Me.Range("a1"), _
SearchDirection:=xlPrevious).Row

Set myRng = Me.Range("D3", Me.Cells(l_LastRow, "D"))

On Error GoTo errHandler:

With Target
If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Offset(0, 1).Value) _
And IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = Target.Offset(0, 1).Value * Target.Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Coolboy55 wrote:
>
> Can someone tell me how to correct my syntax in the following code?
> Right now it does nothing. Thanks!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim l_LastRow As Long
>
> l_LastRow = Cells.Find(What:="*", After:=[A1],
> SearchDirection:=xlPrevious).Row
>
> For x = 3 To (l_LastRow - 1) Step 1
> If Target.Address = Cells(x, 4) Then
> Cells(x, 5).Value = Cells(x, 5).Value * Cells(x, 4).Value
> End If
> Next x
>
> End Sub
>
> --
> Coolboy55
> ------------------------------------------------------------------------
> Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
> View this thread: http://www.excelforum.com/showthread...hreadid=397926

--

Dave Peterson