Check whether header contains a string in Excel

Help
, , ,
1

I would like to check whether a row from one table is in the header of another table.

The first table have a column with the rows like this:
N1
N_3
N8

and so on.

The header of another table is: Parameter N1, N_3, Parameter N8.

I use this method in IF:

b3_dt.Columns.Contains(row(0).ToString)

It manages to understand than “N_3” is present in the header. But he fails to find out that “N1” and “N8” are also in the header. It seems that the extra word “Parameter” is the reason. How do I check it correctly?

2

You can use below expression in If condition

2 Likes
3

@Bharat thank you very much, it really works. Could you, however, give me a more detailed explanation of your code? I don’t really understand what the “Cast” method does and why you use “Count” at the end. Could you please explain the logic?

4

Cast is used to change the type of element of collection returned by dt.columns.
Expression will return all the column names which contains your input search string, I have just checked the number of columns which finds this contains condition true.
If count of column names having input search string is greater than zero then execute true part else move to false part.
Thanks

5

Thank you.
To which type Cast will change?
Which type will the ‘x’ variable have inside the ‘Where’ part?

6

X is of type System.Data.DataColumn inside where part.
If you wants to study more, you can search lambda expression in vb.net and you can also study about LINQ.
Thanks

7

@Bharat, yeah okay thank you very much

8

@Bharat, Sorry for asking you again but could please also say whether it’s possible to get the index or the name of the column which contains the row(0).ToString?

9

You can use below expression to get column name:
b3_dt.Columns.Cast(Of system.Data.DataColumn).Where(function(x) x.ColumnName.Contains(row(0).toString))(0)

To get the index, you can pass the name obtained from above in the method below:

dt.columns.indexof(colname)
Regards
Bharat Kumar

1 Like
10

@Bharat, thank you very much, I got it