Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Converting VBA to Fortran

  1. Nov 18, 2011 #1
    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

    Application.ScreenUpdating = True

    End With

    End Sub
  2. jcsd
  3. Nov 18, 2011 #2


    User Avatar
    Science Advisor
    Homework Helper

    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.
  4. Nov 21, 2011 #3
    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!
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook