Event handler, trigger vba to run

Godders199

Active Member
Joined
Mar 2, 2017
Messages
302
Office Version
  1. 2013
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Active Member
Joined
Mar 2, 2017
Messages
302
Office Version
  1. 2013
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:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,237
Messages
5,623,542
Members
415,977
Latest member
Tommyboy30

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top