Run Code only if Header contains multiple values in different cells

[silentwolf]

Hi guys,

it has been a while since I have been here so I hope all of you are well!

In my worksheet first Row are about 10 columns filled with header data.
I like to create a condition so the code only runs if those cells have certain values.
For example
A1 (IBAN)
B1 (Auszugsnummer)
C1 (Buchungsdatum)
D1 (Valudadatum)
E1 (Umsatzzeit)
and so on..

So what could be the easiest or the best way of creating a conditon so the Code only runs if all 10 Cells in the header row meat this condition if not run a different code or exit out of it.

I found some help on here where the first rows are populated in a string Array.

Sub datacollect()
Dim heading() As String
Dim i As Integer
Dim x As Variant

i = -1
For Each x In rows(1).Cells
    If x.value = "" Then Exit For
    i = i + 1
    ReDim Preserve heading(i) As String
    heading(i) = x.value
Next x
End Sub

Something like that.. but how can I put that all together to create a safeguard so the Code wont run if those header cells are not given or different pupulated.

Hope this is understandable and someone could give me a littel help with it.

Many Thanks

Albert

 

[SQUIDD]

Hi Albert

Could you not just have aa helper cell with formula in, formula checks the conditions, then code runs if formule is true etc?

Or if not liking formula route.

maybe something like, but maybe too long winded.

Sub albert()
tot = 0
    If Range("a1") = "IBAN" Then tot = tot + 1
    If Range("b1") = "Auszugsnummer" Then tot = tot + 1
    If Range("c1") = "" Then tot = tot + 1
    If Range("d1") = "" Then tot = tot + 1
    If Range("e1") = "" Then tot = tot + 1
    If Range("f1") = "" Then tot = tot + 1
    If Range("g1") = "" Then tot = tot + 1
    If Range("h1") = "" Then tot = tot + 1
    If Range("i1") = "" Then tot = tot + 1
    If Range("j1") = "" Then tot = tot + 1
    If tot = 10 Then runmycode
End Sub

 

[silentwolf]

Hi Albert

Could you not just have aa helper cell with formula in, formula checks the conditions, then code runs if formule is true etc?

Or if not liking formula route.

maybe something like, but maybe too long winded.

Sub albert()
tot = 0
    If Range("a1") = "IBAN" Then tot = tot + 1
    If Range("b1") = "Auszugsnummer" Then tot = tot + 1
    If Range("c1") = "" Then tot = tot + 1
    If Range("d1") = "" Then tot = tot + 1
    If Range("e1") = "" Then tot = tot + 1
    If Range("f1") = "" Then tot = tot + 1
    If Range("g1") = "" Then tot = tot + 1
    If Range("h1") = "" Then tot = tot + 1
    If Range("i1") = "" Then tot = tot + 1
    If Range("j1") = "" Then tot = tot + 1
    If tot = 10 Then runmycode
End Sub

Hi SQUIDD,

thanks for your reply!
Unfortunatelly I like to have it all automated so a Formula is not the way I would like to go.
However could you not just somehow create an array and then compare it with the first row of the worksheet somehow?

I am not that good in arrays but that what I was thinking and been searching for I guess..

But many thanks for your code and input!!

Cheers Albert

 

[mohadin]

Hi
Try this method

Sub test()
    Dim a, b
    Dim i&
    a = Application.Transpose(Cells(1, 1).Resize(, 10))
    b = [{"IBAN","Auszugsnummer","Buchungsdatum","Valudadatum","Umsatzzeit"}] '<< fill as required
    For i = 1 To UBound(b)
        If Not IsNumeric(Application.Match(b(i), a, 0)) Then
            Exit Sub 'Or do something
        End If
    Next
End Sub

 

[SQUIDD]

Hi Albert

If i understand you correctly?

Sub albert_2()
    my_arr = ""
    mystr = ",IBAN,Auszugsnummer,Buchungsdatum,Valudadatum,Umsatzzeit" 'hardcode list,change to suit
    'make the header array
        For a = 1 To 10
            my_arr = my_arr & "," & Cells(1, a)
        Next a
    If my_arr = mystr Then runmycode
End Sub

 

[silentwolf]

Hi Albert

If i understand you correctly?

Sub albert_2()
    my_arr = ""
    mystr = "IBAN,Auszugsnummer,Buchungsdatum,Valudadatum,Umsatzzeit," 'hardcode list,change to suit
    'make the header array
        For a = 1 To 10
            my_arr = my_arr & "," & Cells(1, a)
        Next a
    If my_arr = mystr Then runmycode
End Sub

Hi Squidd,

thank you for your code I just have to work it out to run it correctly but many thanks!
I will get back to you if I get it to run

Cheers
Albert

 

[Peter_SSs]

Would something like this work for you? I have just done it for the 5 columns you listed so you would need to expand it if you have 10 columns.

Sub Run_If()
  If Join(Application.Index(Range("A1:E1").Value, 1, 0), "|") = "IBAN|Auszugsnummer|Buchungsdatum|Valudadatum|Umsatzzeit" Then
    'Run the code here
  End If
End Sub

 

[SQUIDD]

Hi Squidd,

thank you for your code I just have to work it out to run it correctly but many thanks!
I will get back to you if I get it to run

Cheers
Albert

No Problem Albert.

I did edit it to put a , at the start of mystr.

What calls this code to check the headers? button? sheet change?

 

[silentwolf]

Would something like this work for you? I have just done it for the 5 columns you listed so you would need to expand it if you have 10 columns.

Sub Run_If()
  If Join(Application.Index(Range("A1:E1").Value, 1, 0), "|") = "IBAN|Auszugsnummer|Buchungsdatum|Valudadatum|Umsatzzeit" Then
    'Run the code here
  End If
End Sub

Hi Peter,
thanks to you for the code!
Well unfortunatelly it never gets into the debug.print statement

Sub Run_If()
  If Join(Application.index(Range("A1:I1").value, 1, 0), "|") = "IBAN|Auszugsnummer|Buchungsdatum|Valudadatum|Umsatzzeit|Zahlungsreferenz|Waehrung|Betrag|Buchungstext|Umsatztext" Then
    'Run the code here
    Debug.Print "Alles gut"
  End If
End Sub

What am I missing?

Cheers
Albert

 

[Peter_SSs]

Well unfortunatelly it never gets into the debug.print statement

Do the headers have to be in the order you gave in post #1?

silentwolf.xlsm
	A	B	C	D	E
1	IBAN	Auszugsnummer	Buchungsdatum	Valudadatum	Umsatzzeit
Sheet1

Or could they be in any order like this?

silentwolf.xlsm
	A	B	C	D	E
1	Buchungsdatum	IBAN	Umsatzzeit	Valudadatum	Auszugsnummer
Sheet1 (2)

.. or something else altogether?