Excel VBA to tell if a column has moved?

[Phil Payne]

Hello all,

I have a worksheet (.xls) which is provided by an external group from their database.
When we run a macro to update our worksheet it opens the supplied worksheet and takes the data from its column "H".

I need to provide a warning to our group members (msgbox) that column H has been moved or omitted the instant our macro has been launched and then end the macro safely.

Points to note:

• Column H heading is "Cumulative to date" but the heading may be in any row of that column H.
• We have no way of influencing how the output from the database is produced.

Can anyone tell me the vba code that can determine if Column H has been moved or omitted?

Thanks in advance.

 

[Comfy]

Sub FindHeader()
Dim cfound As Range
Set cfound = Nothing


       Set cfound = Columns("H:H").Find(What:="Cumulative to date", After:=Range("H1"), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
        If cfound Is Nothing Then MsgBox "Column H has moved": Exit Sub
        
        


End Sub

 

[AlphaFrog]

Maybe something like this...

    [color=darkblue]If[/color] IsError(Application.Match("Cumulative to date", ActiveSheet.Range("H:H"), 0)) [color=darkblue]Then[/color]
        MsgBox "'Cumulative to date' is not in column column H. ", vbExclamation, "Process Aborted"
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]

 

[Phil Payne]

Thank you Comfy, this worked well.

I'd mark this as solved but cant see how!

Regards,

Sub FindHeader()
Dim cfound As Range
Set cfound = Nothing


       Set cfound = Columns("H:H").Find(What:="Cumulative to date", After:=Range("H1"), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
        If cfound Is Nothing Then MsgBox "Column H has moved": Exit Sub
        
        


End Sub

 

[Phil Payne]

Thanks AlphaFrog