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

 

[silentwolf]

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

Hi Mohadin,

did not see your reply sorry!
I will try that too many thanks for your input!

Cheers

Albert

 

[silentwolf]

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?

the order is like that

IBAN, Auszugsnummer, Buchungsdatum, Valutadatum, Umsatzzeit, Zahlungsreferenz, Waehrung, Betrag, Buchungstext, Umsatztext

P.S how to I get the Excel view in hiere again?

 

[Peter_SSs]

Well unfortunatelly it never gets into the debug.print statement

That is because your code uses A:I1 which is 9 cells but the string you are comparing it to contains 10 values.
Should be A1:J1

 

[silentwolf]

That is because your code uses A:I1 which is 9 cells but the string you are comparing it to contains 10 values.
Should be A1:J1

D'oh

now it works yes!!
Many thanks Peter!!!

If I like to create a set of different "Headings" how would that be the best approach to do please?

Chees
Albert

 

[Peter_SSs]

D'oh

now it works yes!!
Many thanks Peter!!!

Cheers.

If I like to create a set of different "Headings" how would that be the best approach to do please?

You would have to explain exactly what you mean by that.

 

[silentwolf]

Cheers.


You would have to explain exactly what you mean by that.

Well I have different worksheets with different headings..
I would like to Run one Prozedur and that prozedure checks which "worksheet" it is
for example if another workheet has
IBAN, Buchungsdatum, Umsatztext, Zusatztext, Texte, RechNr, Betrag, GegenKonto
then the Code needs to run differently

So I like to let the system check what kind of "Document" it is according to the header Row
If the first example is met then run CodeA
if the second is met then run CodeB

and so on..

Because I like to run it in the background update the Document as needet, save the file and then I import those files into Access..

 

[Peter_SSs]

I would like to Run one Prozedur and that prozedure checks which "worksheet" it is
for example if another workheet has
IBAN, Buchungsdatum, Umsatztext, Zusatztext, Texte, RechNr, Betrag, GegenKonto
then the Code needs to run differently

So it seems the different types of documents have a different number of column headings to check the headings in. In that case you could use something like this.

Sub Run_If_v2()
  Select Case True
    Case Join(Application.Index(Range("A1:J1").Value, 1, 0), "|") = "IBAN|Auszugsnummer|Buchungsdatum|Valudadatum|Umsatzzeit|Zahlungsreferenz|Waehrung|Betrag|Buchungstext|Umsatztext"
      CodeA
    Case Join(Application.Index(Range("A1:H1").Value, 1, 0), "|") = "IBAN|Buchungsdatum|Umsatztext|Zusatztext|Texte|RechNr|Betrag|GegenKonto"
      CodeB
  End Select
End Sub

 

[silentwolf]

Hi Peter,

many thanks yes I will try that!

Cheers and nice Sunday!

Albert