0

I want to look up the column number that contains a specific header. I've wrote a couple of simple lines. However, even this is giving me a run-time error of 1004, application-defined or object defined error. What am I doing or not doing that is causing this issue? Please help. Thank.

Private Sub worksheet_Change(ByVal target As Range)

Dim col As Long

col = Range("A1:A4").Find("abc", , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column

Cells(1, 6).Value = col

End Sub
Community
  • 1
  • 1
D1020
  • 1
  • 1
  • When you do get this working, you will find yourself in a infinite loop and your excel will crash. Turn off **events** and reenable before ending sub. – urdearboy Jun 21 '18 at 00:56
  • You are only looking in one column?? – Gary's Student Jun 21 '18 at 01:42
  • I'm just using 1 column as a test. BTW, thank you urdearboy for the advice. turning off events worked. However, why would I get an infinite loop if I didn't turn off events? Can you explain it? – D1020 Jun 21 '18 at 01:49
  • [This link](https://stackoverflow.com/a/50868969/6706419) will give you an explanation for the infinite loop. – urdearboy Jun 21 '18 at 01:51

0 Answers0