PDA

View Full Version : [SOLVED] Needs Help With For Next Loop



ham123
04-03-2019, 08:52 PM
Greetings experts,

I need help editing this code into a for next loop.

If Activesheet's Cell B11 is the same as any value in Column F from sheet "Vendor Code List" then Activesheet's Cell B13 value would be the value in Column E from sheet "Vendor Code List" corresponding to the same row.


Sub AutoPopulate()


If Range("B11") = Worksheets("Vendor Code List").Range("F") Then
Range("B13") = Worksheets("Vendor Code List").Range("E")
End If

End Sub


Any help is much appreciated! :)

大灰狼1976
04-03-2019, 09:40 PM
Sub AutoPopulate()
Dim rng As Range
Set rng = Sheets("Vendor Code List").Columns("f").Find([b11], lookat:=xlWhole)
If Not rng Is Nothing Then [b13] = rng.Offset(, -1)
End Sub

ham123
04-03-2019, 09:45 PM
For some reason, I can't find any.. My cell B11 in the first worksheet has a drop down data validation, is that the problem?

大灰狼1976
04-03-2019, 09:52 PM
Please refer to the attachment.

ham123
04-03-2019, 11:32 PM
I have tried again but I still get this error
24004

大灰狼1976
04-03-2019, 11:40 PM
Sorry, I don't know the reason, it worked well here.

大灰狼1976
04-04-2019, 12:58 AM
Maybe it's a problem caused by the use of "[ ]".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> Range("b11").Address Then Exit Sub
If Target = "" Then Exit Sub
AutoPopulate
End Sub
Sub AutoPopulate()
Dim rng As Range
Set rng = Sheets("Vendor Code List").Columns("f").Find(Range("b11"), lookat:=xlWhole)
If Not rng Is Nothing Then Range("b13") = rng.Offset(, -1)
End Sub

ham123
04-04-2019, 01:13 AM
Hi,

I know what was the problem. It was because I had other objects in the field and I saw that they clashed into the code when I debug.print