Need Help With Auto Populate

[ham123]

Greetings experts,

I have this code which I populate some cells and it works for the AutoPopulateExistingVendorCode but not for the AutoPopulateAvailability. For the AutoPopulateAvailability, I am trying to populate cell D20 based on cell D18, the value in cell D18 will exist in another sheet called "IO Number" and cell D20 will be populated based on cell D18's column G value in this sheet.

Private Sub Worksheet_Change(ByVal Target As Range)





If Target.Count > 1 Then Exit Sub

If Target.Address <> [D10].Address Then Exit Sub

If Target = "" Then Exit Sub

AutoPopulateExistingVendorCode



If Target.Count > 1 Then Exit Sub

If Target.Address <> [D18].Address Then Exit Sub

If Target = "" Then Exit Sub

AutoPopulateAvailability



If Range("D16").Value > 1000 Then

MsgBox ("Please request a new vendor code from ESP before continueing")

End If



End Sub



Sub AutoPopulateExistingVendorCode()

Dim rng As Range

Set rng = Sheets("Vendor Code List").Columns("f").Find([D10], lookat:=xlWhole)

If Not rng Is Nothing Then [D12] = rng.Offset(, -1)

End Sub



Sub AutoPopulateAvailability()

Dim rng As Range

Set rng = Sheets("IO Number").Columns("a").Find([D18], lookat:=xlWhole)

If Not rng Is Nothing Then [D20] = rng.Offset(, 5)

End Sub

Any help is much appreciated!

 

[p45cal]

Presumably there's no error being thrown up. In that case then nothing's being found, and you think it should find something?
1. You need to check manually that what you're searching for is indeed to be found; no extra spaces and suchlike in either what's sought or what's being searched.
2. The range,find method has a few more arguments than you're using. Some are needed to ensure that any previous find operation, whether done manually on the sheet or done elsewhere in code. The LookIn, LookAt and SearchFormat arguments are remembered between Finds, so it's a good idea always to include them.
Also, the LookIn argument is important, especially if you have data which have been filtered. If you are searching cells that do NOT have formulae in them then:
xlValues searches only visible cells
xlFormulas searches all the values whether in hidden cells or not
but if the cells you're searching do have formulae in them and you're searching for something in the results of the formulae, you have to use xlValues and ensure that the cells you want to search through are visible (eg. not filtered).
Of course, if you want to search the formulae themselves (unlikely) you have to use xlFormulas.

You probably want to set the SearchFormat argument to False everytime.

It's a good idea to apply this sort of thing to all range.find statements in the code.

Then you need to test again, but do so by putting a breakpoint somewhere early in the Worksheet_Change event handler then following step by step with repeated presses of F8 on the keyboard, keeping an eye on the Locals pane in the VBE to observe that rng is having something assigned to it.

 

[ham123]

Hi, thank you for your reply!

Can you help me debug the error as after checking, I still don't know why it works for one but not the other.
I have attached an example file here.

 

[p45cal]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
On Error GoTo Here
Application.EnableEvents = False
If Target.Address = [D10].Address Then AutoPopulateExistingVendorCode
If Target.Address = [D18].Address Then AutoPopulateAvailability
If Range("D16").Value > 1000 Then MsgBox ("Please request a new vendor code from ESP before continueing")
Here:
Application.EnableEvents = True
End Sub

 

[ham123]

@p45cal Thank you for all your help!

The code you provided in the previous comment worked for me