Converting VBA to Fortran

[zakynthos]

Hi, I've no knowledge of Fortran but am researching its (theoretical) use to perform a 'cross- referencing' function on data in a four column array of one million rows. The program matches data in column A to Column D by referencing the matching values in columns B and C.

Here's the program I'm using - could anyone convert this to Fortran and could this in, in practice, run on Fujitsu's K (10 teraflops) computer. If so, how long would it take the K to perform this task - I've calculated 12.93 hrs from tests on a typical office PC? With thanks.

Sub AddCodes()

Dim x As Long, LastRow As Long, UB As Long, BList As Variant

Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

BList = Join(WorksheetFunction.Transpose(Cells(StartRow, "B").Resize(LastRow - StartRow + 1)), "/")
' Application.ScreenUpdating = False

For x = StartRow To LastRow
UB = UBound(Split(Split(BList, Cells(x, "C").Value)(0), "/"))
If UB >= 0 Then
With Cells(x, "D")
.Value = .Value & "," & Range("A1").Offset(UB).Value
If Left(.Value, 1) = "," Then .Value = Mid(.Value, 2)
.Interior.ColorIndex = 6
.Font.Bold = True
End With
End If
Next

Application.ScreenUpdating = True

End With

End Sub

[AlephZero]

If it takes 12 hours on a PC your algorithm is hopelessly inefficient.

If you first sorted the table on the relevant columns and then did the matching, I would expect the run time to be of the order of 1 second not 12 hours.

Relational database operations are a good way to describe what you want to do, but they are often a bad way to actually do it.

[zakynthos]

Many thanks for your answer - your advice made me re-think the code and I've processed the array with vlookup instead and yes, you're right of course, about a second!

However, I'm still interested to knnow how my original code qwould translate into Fortran.

Thanks once again!