Target.Address syntax

C

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
 
A

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 th
Target cell. From memory, the argument 'external' by default is Fals
giving you the cell address.

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

Hope this helps!


Best regards


Deepak Agarwa
 
D

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


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
Click to expand...
 
Top