Search for multiple string values in the first row of a table and format column values.

Hello all, I'm having a problem searching for multiple string values in the top row of a table and return the column number.

I want to search for all the table column headers one by one and numberformat the corresponding column values.
I managed to search for the first string value and to set the numberformat of the column values but now i want to do the same for the next 59 colums.
Ofcourse, I could repeat this code 59 times, but I guess there is a better way to do it. (e.g. using a loop)

I would realy appreciate if someone could help.

[code]
Dim cellToSearch As Range
Dim Found As Range
Dim foundColumn As Long
Dim searchValue As String
Dim nSmp As Long

nSmp = 60 'Number of sample columns

With Worksheets("Sheet2")

Set cellToSearch = Sheets("Sheet2").Range(.Cells(1, 1), .Cells(1, nSmp))

searchValue = "Actual AdBlue dosing rate[g/h]"
Set Found = cellToSearch.Find(what:=searchValue)

If Found Is Nothing Then

Else
foundColumn = cellToSearch.Find(what:=searchValue).Column

Sheets("Sheet2").Cells(1, foundColumn).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
End If

End With

Application.ScreenUpdating = True

End Sub
'List of string values to search for
'Actual AdBlue dosing rate[g/h]
'Actual AdBlue dosing rate[oz/h]
'Actual fuel quantity[g/stroke]
'Actual HC dosing rate[g/h]
'Actual HC dosing rate[oz/h]
'AdBlue dosing valve position[-]
'AdBlue tank heater valve position[-]
'AdBlue tank level[%]
'AdBlue tank temperature[C]
'AdBlue tank temperature[F]
[\code]

Thanks in advance!

Hi

you should use the cells function.

Please Login or Register  to view this content.

You can put your "headers" and "formats" into arrays or into a collection so that you can read then off using the same counter

AB33, thank you very much. It works like a charm!

I'm getting more experienced in VBA macro's but looking at your solution I still have a long way to go.

Thanks!!!

Excel,
You are welcome!