Event handler, trigger vba to run

[Godders199]

Hello, I am trying to get the follow function to work with no success and looking for some help.
effectively what I am trying to achieve is, if a value is added to a cell in column 19, a vlookup is performed and the result added to column 17.

option compare text
private sub worksheetChange (byval target as range)

dim lastrowcolumnb as long
lastrowcolumnb = range (“p” & rows.count).end(xl up).row
for b = 2 or lastrowcolumnb

if instr(1. cells (b,19),”<>”)>0 then
cells(B,17)=application.worksheet function.vlookup(cells(b,19),sheets(“active rm”).range(“a:c”),2,false)
else
end if
next
sub end

currently with cells b,19 set to look for non blank, it returns nothing.
if I change it to a value held in the active rm sheet, I get an method range of object_worksheet failed on the vlookup
but can’t see thing wrong.

any tips or alternative methods would be appreciated.

 

[Joe4]

You have all sorts of errors in your code, including:
- slanted quotation (“) marks instead of straight ones (")
- spaces where there shouldn't be any
- "Sub End" instead of "End Sub"

See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

'   Only run if single cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if cell in column 19 updated
    If Target.Column <> 19 Then Exit Sub

'   Get row that was updated
    r = Target.Row
    
'   Update column 17 with VLOOKUP function
    Cells(r, 17).Formula = Application.WorksheetFunction.VLookup(Cells(r, 19), Sheets("active rm").Range("a:c"), 2, False)

End Sub

 

[Godders199]

You have all sorts of errors in your code, including:
- slanted quotation (“) marks instead of straight ones (")
- spaces where there shouldn't be any
- "Sub End" instead of "End Sub"

See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

'   Only run if single cell updated
    If Target.CountLarge > 1 Then Exit Sub
   
'   Only run if cell in column 19 updated
    If Target.Column <> 19 Then Exit Sub

'   Get row that was updated
    r = Target.Row
   
'   Update column 17 with VLOOKUP function
    Cells(r, 17).Formula = Application.WorksheetFunction.VLookup(Cells(r, 19), Sheets("active rm").Range("a:c"), 2, False)

End Sub

Thanks will give it ago. Apologies for errors in my code I can not access this site through work so was typing it out.