Help!!! Find column and define with name

[excel199]

I have a problem with my code. First off I am new to coding. Researched different sites online and the code I wrote is the best I came up with.
What I am trying to do with my code is search for the column name in Row 1 of each column (A1, B1, C1, ect..). After it finds the first two or three characters of the name of the column. Select that column and define that column by the name I supplied. So I can use that new define name in a formula I created. The problem that I have is I have two column with similar names and it always define the one that I do not want. One column is (B1) "KbTVD" the one I dont want defined and (F1) "TVD" the one I need. Every time I run my code it selects and defines B1 and I need F1 to be defined instead. Also I need my code to search different row 1 columns just for "TVD" because it is not a guarantee that "TVD" will always be in F1.

Code:

Sub DATA()  

    Set fnd = Range("1:1").Find("on*")   
    ActiveWorkbook.Names.Add "ono", fnd.EntireColumn, True

    Set fnd = Range("1:1").Find("tvd")
    ActiveWorkbook.Names.Add "tvd", fnd.EntireColumn, True

End Sub

this code selects and defines B1

Sub DATA()  

    Set fnd = Range("1:1").Find("on*")   
    ActiveWorkbook.Names.Add "ono", fnd.EntireColumn, True
    
    Set fnd = Range("1:1").Find("tvd", , , , , , True) ' 
    ActiveWorkbook.Names.Add "tvd", fnd.EntireColumn, True


End Sub

I tried to rewrite it and I get a error

run-time error '91':

object variable or with block variable not set


Also do I need to Dim something in my code?

 

[oldbrewer]

				abc1	abc	1abc
				4	5	6
		abc	5
		formula giving 5
		=OFFSET(I38,1,MATCH(H43,J38:L38,0))

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[excel199]

I do not understand this code I believe that it did not come out correct on your end. can you resend or anyone else can you please help?

 

[oldbrewer]

offset means go down some then along some

so you start from (my example) I38, you go down 1, how far along is match(text in H43 with range J38:L38

you are looking for a column header, these are unique, so if one is apple and one ia apple1 if you match for apple you will find apple

 

[excel199]

offset means go down some then along some

so you start from (my example) I38, you go down 1, how far along is match(text in H43 with range J38:L38

you are looking for a column header, these are unique, so if one is apple and one ia apple1 if you match for apple you will find apple

ok I understand the offset function, but what I am not understanding is the range you picked. The string data will only be in row 1 of every column, and the data that I receive will not always be in the same column when I open it in different spreadsheets. And I will not know the length of the columns for each spreadsheet. I was trying to write a code that will specifically look for "TVD" only in row 1 of each column. and skip the string "KBTVD" until it sees the "TVD".

 

[oldbrewer]

so the offset will be from $A$1, down=up to you, along = match("TVD",$B$1:$CZ$1,0)

so today tvd is in column C tomorrow in column E you will find it ok wherever it is