Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert more than 1 Row Syntax | Excel Worksheet Functions | |||
Rate syntax | Excel Discussion (Misc queries) | |||
WHAT SYNTAX IS USED TO ROUND A 4-DIGIT NUMBER TO THE NEAREST THOUS | Excel Discussion (Misc queries) | |||
Vlookup Syntax Error | New Users to Excel | |||
Previous Post - Correct Syntax Query | Excel Worksheet Functions |