Target.Address syntax
 

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

Maybe:

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

Regards

Trevor


"Coolboy55" wrote
in message ...

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

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


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=397926

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