Looping through a flat tabled spreadsheet

[Asterix]

Spreadsheet is a simple table, each row is a record, each column cell within each row is a field for that record.

I've applied a named range to each column (e.g. A:A = "Event", B:B is "Name_class", M:M is "Novice_or_expert").

I'm trying to loop through the non-blank records from row 2 onwards (row 1 is a header) and apply a value to each "Novice_or_expert" field based on using name_class as a vlookup into another sheet.

Code so far is...

For Each c In Range("name_class")

If c.Value <> "" And c.Value <> "Name & WSCC class" Then

If novice_or_expert.Value = "" Then novice_or_expert.Value = Application.WorksheetFunction.VLookup(c.Value, Worksheets("Lookup data - input").Range("B:E"), 4, False)

End If

Next

...but that doesn't work (and I know that it's because the novice_or_expert.value reference is fundamentally wrong). Problem is, not sure how to put this simple problem right. Have been searching but can't find solution.

Any help gratefully received.

[Bob Phillips]

[vba]

Dim c As Range

For Each c In Range("Name_class")

If c.Value <> "" And c.Value <> "Name & WSCC class" Then

If Range("Novice_or_expert").Cells(c.Row, 1).Value = "" Then _
Range("Novice_or_expert").Cells(c.Row, 1).Value = Application.VLookup(c.Value, Worksheets("Lookup data - input").Range("B:E"), 4, False)
End If
Next
[/vba]

[Asterix]

thx m8e, most kind

[Asterix]

In a similar vein, what's the correct version of

Cells(rowcounter, 10).Value = Application.WorksheetFunction.Min("timed1:timed5")

...where "timed1", "timed2", "timed3", "timed4" and "timed5" are named ranges representing 5 contiguous columns?

Ta

[Asterix]

Sorted that, thankyou

[lucas]

please post your solution for others who might find this thread in a search.

[Asterix]

I did this, though there's probably a smarter way...

If Cells(c.Row, 10).Value = "" Then
If Application.WorksheetFunction.IsText(Range("timed1").Cells(c.Row, 1).Value) Or _
Range("timed1").Cells(c.Row, 1).Value = 0 Then t1 = 999 Else _
t1 = Range("timed1").Cells(c.Row, 1).Value
If Application.WorksheetFunction.IsText(Range("timed2").Cells(c.Row, 1).Value) Or _
Range("timed2").Cells(c.Row, 1).Value = 0 Then t2 = 999 Else _
t2 = Range("timed2").Cells(c.Row, 1).Value
If Application.WorksheetFunction.IsText(Range("timed3").Cells(c.Row, 1).Value) Or _
Range("timed3").Cells(c.Row, 1).Value = 0 Then t3 = 999 Else _
t3 = Range("timed3").Cells(c.Row, 1).Value
If Application.WorksheetFunction.IsText(Range("timed4").Cells(c.Row, 1).Value) Or _
Range("timed4").Cells(c.Row, 1).Value = 0 Then t4 = 999 Else _
t4 = Range("timed4").Cells(c.Row, 1).Value
If Application.WorksheetFunction.IsText(Range("timed5").Cells(c.Row, 1).Value) Or _
Range("timed5").Cells(c.Row, 1).Value = 0 Then t5 = 999 Else _
t5 = Range("timed5").Cells(c.Row, 1).Value
Cells(c.Row, 10).Value = Application.WorksheetFunction.Min(t1, t2, t3, t4, t5)
If Cells(c.Row, 10).Value = 999 Then Cells(c.Row, 10).Value = "NTR"
End If