CesarModule

' This module contains method for copying and referencing data

' Updates the selected items from H:12 to  H:25
Sub UpdateSelected()
    Application.Calculation = xlManual
    ' First Check update productioin escenarios
    'If Range("Upd_Scenarios") = "Yes" Then
        'Call UpdateProductionScenaries
    'End If
    
    If Range("Upd_Control") = "Yes" Then
        Call UpdateControlAssumptions
    End If
    
    If Range("Upd_Proydata") = "Yes" Then
        Call UpdateProjectData
    End If
    
    If Range("Upd_Regime") = "Yes" Then
        Call UpdateRegimeImport
        Call UpdateRegimeImport_2
    
    End If
    
    If Range("Upd_Fiscal") = "Yes" Then
        Call UpdateCountriesLinks
    End If
    
    
    Application.Calculation = xlSemiautomatic
End Sub


' List all the tabs with a defined color in this case
' Updates the production scenaries in the Control tab
Sub UpdateProductionScenaries()
    sNames = SheetNames()
    prodTabColor = 5287936
    
    row = 0
    col = Range("M1").Column
 
    For i = 0 To Sheets.Count - 1
        If Sheets(sNames(i)).Tab.Color = prodTabColor Then
            Sheets("Control").Cells(50 + row, col).Value = sNames(i)
            row = row + 1
        End If
    Next i
    
End Sub

' Updates the control Asumptions link for each country
Sub UpdateControlAssumptions()
    countryTabColor = 65535
    Dim sName As String
    Dim sNumber As Integer
    
    ' Clear Errors that are listed
    sNumber = 1
    
    For Each sheet In Sheets
        If sheet.Tab.Color = countryTabColor Then
            Call SetCountryControlAssumptions(sheet.Name, sNumber)
        End If
    Next sheet
    
End Sub

' Creates links from Range("ProjectData") to the cells in the country
Sub UpdateProjectData()
    countryTabColor = 65535
    Dim sName As String
    Dim sNumber As Integer
    
    ' Clear Errors that are listed
    row = Range("projectDataErrors").row
    col = Range("projectDataErrors").Column
    
    Sheets("Input Data").Range(Cells(row, col), Cells(row + 200, col)).ClearContents
    sNumber = 1
    
    For Each sheet In Sheets
        If sheet.Tab.Color = countryTabColor Then
            Call SetCountryProjectData(sheet.Name, sNumber)
        End If
    Next sheet
    
End Sub

' Updates the "RegimeImport" tab with the data from other countries
Sub Calculate_multiscenario()
 
Application.ScreenUpdating = False
'Application.DisplayStatusBar = False
'Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If Range("Multiscenario_table") = "Inactive" Then
       Call Table_4_On
End If
If Range("Multiscenario_report") = "Active" Then
        Call Report_Multiscenario_generator_off
End If
Range("mode_chosen") = 1

Application.Calculate

Application.Calculation = xlCalculationSemiautomatic

Call Table_4_Off
Call Report_Multiscenario_generator_on

Application.Calculation = xlCalculationSemiautomatic
Application.ScreenUpdating = True
'Application.DisplayStatusBar = True
'Application.EnableEvents = True

Beep

 
End Sub



'***************************************
'***************************************




'***************************************
'***************************************

Sub UpdateRegimeImport()

    Application.Calculation = xlManual
    
    countryTabColor = 65535
    Dim sName As String
    Dim col As Integer
    
    ' Clear the previous regime imports
    Sheets("Result").Range("E13:AH49").ClearContents
    col = Range("E1").Column
    
    For Each sheet In Sheets
        If sheet.Tab.Color = countryTabColor Then
            Debug.Print "Regime import for " & sheet.Name
            Call LinkCountryRegimeImport(sheet.Name, col)
            col = col + 1
        End If
    Next sheet

    govRevTxt = "Government revenues Positions"
    resTxt = "Results Positions"
    col = 5
    
    Sheets("Result").Range("E72:AH115").ClearContents
    
    For Each sh In Sheets
        If sh.Tab.Color Then
        
            rowGovRev = FindRowInSheet(sh, 4, govRevTxt)
            If rowGovRev > 0 Then
                Debug.Print "Copy col: " & col & ", page: " & sh.Name
                Call CopyGovRevenuesPositions(sh, col, rowGovRev + 2)
                Call CopyGovRevenueNames(sh, col, rowGovRev + 2)
            End If
            
            rowGovRes = FindRowInSheet(sh, 4, resTxt)
            If rowGovRes > 0 Then
                Call CopyResultsPositions(sh, col, rowGovRes + 2)
                Call CopyResultsNames(sh, col, rowGovRes + 2)
            End If
            
            col = col + 1
        End If
    Next sh
    
    'Call UpdateMultiScenarioLinks_result
    
    Application.Calculation = xlSemiautomatic
End Sub
    

Sub LinkCountryRegimeImport(sheetName As String, col As Integer)
    Dim row As Integer
    Dim rangeSrc As String
    
    row = SearchOutputDataStart(sheetName)
    
    If row > 0 Then
    
        'with direct Links
        
        For i = 0 To 37
            Sheets("Result").Cells(12 + i, col).Formula = "='" & sheetName & "'!C" & (row + i)
        Next i
        
        'With Arrays
        
        'rangeSrc = "={'" & sheetName & "'!C" & row & ":C" & (row + 38) & "}"
        'Sheets("RegimeImport").Range(Cells(5, col + 5), Cells(row + 37, col + 5)).FormulaArray = rangeSrc
        'Else
        
    End If
    
End Sub

' Set the country project data for a sheetName
Sub SetCountryProjectData(sheetName As String, ByRef pos As Integer)
    Dim row As Integer
    row = SearchProjectDataStart(sheetName)
    
    If row > 0 Then
        Sheets(sheetName).Range("G" & row + 1 & ":BH" & row + 28).ClearContents
        Sheets(sheetName).Range("G" & row + 1 & ":BH" & row + 28).FormulaArray = "=ProjectData"
    Else
        Range("projectDataErrors").Cells(pos, 1).Value = "Could not find the Project data in sheet " & sheetName
        pos = pos + 1
    End If
End Sub

' Sets the ControlAssumptions link in the country tab "sheetName"
Sub SetCountryControlAssumptions(sheetName As String, ByRef pos As Integer)
    Dim row, col As Integer
    row = SearchControlAssumptionStart(sheetName)
    col = Range("Q50").Column
    
    If row > 0 Then
        Sheets(sheetName).Range("F" & row + 1 & ":F" & row + 18).FormulaArray = "=ControlAssumptions"
        'Put errors
        If Not (CheckValidListOfAssumptions(sheetName, row + 1)) Then
            Range("controlAssuptionsErrors").Cells(pos, 1) = "List of assumptions is incorrect in " & sheetName
            Debug.Print "sName: " & sheetName & "; pos: " & pos
            
            pos = pos + 1
        End If
    Else
        Range("controlAssuptionsErrors").Cells(pos, 1) = "Could not find the Control assumptions in sheet " & sheetName
        pos = pos + 1
    End If
End Sub


' Searches for first row with value "Control Assumptions (from Control sheet)" in column "D" for the passed sheetName
Function SearchControlAssumptionStart(sheetName As String) As Integer
    SearchStartControlAssumptions = 0
    'Sheets(sheetName).Rows.Count, Not using this because it generates a BIG number, now using 1000
    For i = 1 To 1000
        'If Sheets(sheetName).Cells(i, 4).Text = "Control Assumptions required to drive regime models" Then
        X = Sheets(sheetName).Cells(i, 4).Text
        If Left(X, 19) = "Control Assumptions" Then
            SearchControlAssumptionStart = i
            i = 1000
        End If
    Next i
End Function


' Searches for first row with the value "Project data (from ProjectCashflow sheet)" in column "D" for the passed sheetName
Function SearchProjectDataStart(sheetName As String) As Integer
    SearchStartControlAssumptions = 0
    'Sheets(sheetName).Rows.Count, Not using this because it generates a BIG number, now using 1000
    For i = 1 To 1000
        If Sheets(sheetName).Cells(i, 4).Text = "Project data (from ProjectCashflow sheet)" Then
            Debug.Print "Start project data: " & i
            SearchProjectDataStart = i
            i = 1000
        End If
    Next i
End Function


' Searches for first row with the value "Output Data for mission model" in column "D" for the passed sheetName
Function SearchOutputDataStart(sheetName As String) As Integer
    SearchStartControlAssumptions = 0
    'Sheets(sheetName).Rows.Count, Not using this because it generates a BIG number, now using 1000
    For i = 1 To 2000
        If Sheets(sheetName).Cells(i, 4).Text = "Output Data for mission model" Then
            SearchOutputDataStart = i + 1
            i = 2000
        End If
    Next i
End Function

' Checks that the list of assumptions is correct
Function CheckValidListOfAssumptions(sheetName As String, row As Integer) As Boolean
    CheckValidListOfAssumptions = True
    col = Range("J1").Column
    Dim msg As String
    
    listOfAssumptions = GetListOfControlAssumptions()
    
    For i = 0 To UBound(listOfAssumptions)
      ' Compare values
      If Sheets(sheetName).Cells(row + i, 4).Value <> listOfAssumptions(i) Then
        msg = "Error in CheckValidListOfAssumptions, Sheet: " & sheetName & ", Row: " & (row + i)
        Debug.Print "Error in CheckValidListOfAssumptions, Sheet: " & sheetName & ", Row: " & (row + i)
        msg = msg & " " & "Val should be " & listOfAssumptions(i) & " And is: " & Sheets(sheetName).Cells(row + i, 4).Value
        Debug.Print "Val should be " & listOfAssumptions(i) & " And is: " & Sheets(sheetName).Cells(row + i, 4).Value
        
        CheckValidListOfAssumptions = False
        i = UBound(listOfAssumptions)
      End If
    Next i
End Function


' Returns an array with all the field names in Range("ControlAssumptions")
Function GetListOfControlAssumptions() As Variant
    Dim arr(17) As String
    
    ' End of the Range
    totalRows = Range("ControlAssumptions").rows.Count
    
    col = Range("ControlAssumptions").Column - 1
    
    For i = 1 To totalRows
        arr(i - 1) = Range("ControlAssumptions").Cells(i, -1)
    Next i
    
    GetListOfControlAssumptions = arr
End Function

' Returns an array with all the sheet names
Function SheetNames() As Variant
    Dim arr() As String
    ReDim arr(0 To Sheets.Count - 1)
    
    For i = 0 To Sheets.Count - 1
        arr(i) = Sheets(i + 1).Name
    Next i
    
    SheetNames = arr
End Function


'Show Instruction                             *

Sub showInstruction()
    frmInstruction.Show
End Sub


' Copies the list of countries iterating the sheets in Countries.xlsm file
Sub ImportCountriesLists()
    Dim countries As Workbook
    Dim countRange As Range
    Dim row As Integer
    
    row = 1
    Application.Calculation = xlManual
    
    'Error
    On Error GoTo ErrH
    Set countries = Workbooks("Countries.xlsm")
    Set countRange = Range("countriesList")
    row = 1
    
    countRange.ClearContents
    
    For Each sheet In countries.Sheets
        If sheet.Name <> "Index" Then
            ' Copy name
            Debug.Print sheet.Name
            countRange(row, 1) = countries.Sheets("Index").Cells(12 + row, Range("G1").Column)
            countRange(row, 2) = sheet.Name
            row = row + 1
        End If
    Next sheet
    Application.Calculation = xlSemiautomatic
    
    Exit Sub
ErrH:
    Application.Calculation = xlSemiautomatic
    MsgBox "You must Open the Countries.xlsm file"
    Exit Sub
End Sub


' Copies the list of countries iterating the sheets in Countries.xlsm file
Sub ImportEscenariosList()
    Dim esc As Workbook
    Dim escRange As Range
    Dim row As Integer
    
    Application.Calculation = xlManual
    
    'Error
    On Error GoTo ErrH
    Set esc = Workbooks("Library of Petroleum Fields.xlsm")
    Set escRange = Range("escenariosList")
    row = 1
    
    escRange.ClearContents
    
    For Each sheet In esc.Sheets
        If sheet.Name <> "Index" Then
            ' Copy name
            escRange(row, 1) = esc.Sheets("Index").Cells(12 + row, Range("O1").Column)
            escRange(row, 2) = sheet.Name
            row = row + 1
        End If
    Next sheet
    
    Application.Calculation = xlSemiautomatic
    
    Exit Sub
ErrH:
    Application.Calculation = xlSemiautomatic
    MsgBox "You must Open the Library of Petroleum Fields.xlsm file"
    Exit Sub
End Sub


' Test Compare
Sub TestCompareList()
    listOfAssumptions = GetListOfControlAssumptions()
    If Sheets("Uganda-1").Range("D99").Value <> listOfAssumptions(1) Then
        Debug.Print "Error"
    Else
        Debug.Print "OK"
    End If
End Sub

Sub TestListAssump()
    Dim arr As Variant
    arr = GetListOfControlAssumptions()
    
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next i
End Sub


' Copies the escenarios sheets that have been selected
Sub ImportCountriesSheets()
    Dim dest As Workbook, src As Workbook
    Dim sRange As Range
    
    Application.Calculation = xlManual
    
    lSheet = GetLastCountriesSheet()
    
    Set dest = ActiveWorkbook
    Set src = Workbooks("Countries.xlsm")
    Set sRange = Range("countriesList")
        
    For i = 1 To sRange.Count
        If sRange(i, 2) = "" Then
            Debug.Print "Exit in: " & i & " : " & sRange(i, 2)
            Exit Sub
        End If
        
        If sRange(i, 3).Text = "TRUE" Then
            Debug.Print dest.Sheets(lSheet).Name
            src.Sheets(sRange.Cells(i, 2).Text).Copy After:=dest.Sheets(lSheet)
        End If
    Next i

    Application.Calculation = xlSemiautomatic

    
End Sub



Function GetLastCountriesSheet() As String
    Dim currentSheet As String
    countryTabColor = 65535
    
    For Each sheet In Sheets
        If countryTabColor = sheet.Tab.Color Then
            GetLastCountriesSheet = sheet.Name
        End If
    Next sheet
    
    If GetLastCountriesSheet = "" Then
        GetLastCountriesSheet = "Control"
    End If
End Function


' Copies the escenarios sheets that have been selected

Sub ImportEscenariosSheets()
    Dim dest As Workbook, src As Workbook
    Dim sRange As Range
    
    Application.Calculation = xlManual
    
    lSheet = GetLastEscenarioSheet()
    
    Set dest = ActiveWorkbook
    Set src = Workbooks("Library of Petroleum Fields.xlsm")
    Set sRange = Range("escenariosList")
        
    For i = 1 To sRange.Count
        'If sRange(i, 2) = "" Then
        '    Debug.Print "Exit in: " & i & " : " & sRange(i, 2)
        '    Exit Sub
        'End If
        
        Debug.Print sRange(i, 3).Value
        Debug.Print "Active Workook" & ActiveWorkbook.Name
        Debug.Print "Value: " & sRange(i, 3).Value
        Debug.Print "Value2: " & sRange(i, 3).Value2
        
        If sRange(i, 3).Text = "TRUE" Then
            Debug.Print dest.Sheets(lSheet).Name
            src.Sheets(sRange.Cells(i, 2).Text).Copy After:=dest.Sheets(lSheet)
        End If
        
        dest.Activate
    Next i
    
    Application.Calculation = xlSemiautomatic
End Sub

Sub tests()
Set sRange = Range("escenariosList")
Debug.Print sRange.Cells(1, 3).Text
Debug.Print sRange.Cells(2, 3).Text
Debug.Print sRange.Cells(3, 3).Text

End Sub


Function GetLastEscenarioSheet() As String
    Dim currentSheet As String
    prodTabColor = 5287936
    
    For Each sheet In Sheets
        If prodTabColor = sheet.Tab.Color Then
            GetLastEscenarioSheet = sheet.Name
        End If
    Next sheet
    
    If GetLastEscenarioSheet = "" Then
        GetLastEscenarioSheet = Sheets(1).Name
    End If
End Function



Sub CopySheet()
    Dim dest As Workbook, src As Workbook

    Set dest = ActiveWorkbook
    Set src = Workbooks("Library of Petroleum Fields.xlsm")
    dest.Sheets("Index").Copy After:=dest.Sheets("Control")
End Sub


' Updates the links for Control tab and all countries tab
Sub UpdateCountriesLinks()
    Application.Calculation = xlManual
    Dim tabColor, row As Integer
    Dim val As Variant
    tabColor = 65535
    Dim txt As String
    row = Range("Control_Fiscal_RS").Value
    
    ' Clear old data
    txt = Range(Cells(row, 3), Cells(row + 100, 3)).Address
    Sheets("Control").Range(txt).ClearContents
    'Sheets("Control").Range("C183:C240").ClearContents
    txt = Range(Cells(row, Range("J1").Column), Cells(row + 100, Range("J1").Column)).Address
    Sheets("Control").Range(txt).ClearContents
    
    colE = Range("H1").Column
    Call UnHideRows_3
    
    For Each sheet In Sheets
        If sheet.Tab.Color = tabColor Then
            Sheets("Control").Cells(row, 3).Formula = "='" & sheet.Name & "'!D3"
            val = sheet.Range("F9").Value
            Call UpdateCountryEscenarionLink(sheet.Name, "F" & row)
            'Call UpdateControlCountryRegime(Sheet.Name, row)
            
            Sheets("Control").Cells(row, colE) = val
            Sheets("Control").Cells(row, Range("J1").Column) = sheet.Name
            
            row = row + 1
        End If
    Next sheet
    Call HideRows_3
    Application.Calculation = xlSemiautomatic
End Sub



Sub UpdateCountryEscenarionLink(sheetName As String, pos As String)
    Sheets(sheetName).Range("F9").Formula = "=Control!" & pos
End Sub

Sub UpdateControlCountryRegime(sheetName As String, row As Integer)
    col = Range("H1").Column
    col2 = Range("F1").Column
    
    For j = 0 To 20
        If Sheets(sheetName).Cells(8, 8 + j) <> "" Then
            Sheets("Control").Cells(row, col2 + j) = Sheets(sheetName).Cells(8, 8 + j)
        End If
    Next j
End Sub


' Module to remove all hidden names on active workbook
Sub Remove_Hidden_Names()

   ' Dimension variables.
   Dim xName As Variant
   Dim Result As Variant
   Dim Vis As Variant

   ' Loop once for each name in the workbook.
   For Each xName In ActiveWorkbook.Names

       'If a name is not visible (it is hidden)...
       If xName.Visible = True Then
           Vis = "Visible"
       Else
           Vis = "Hidden"
       End If

       ' ...ask whether or not to delete the name.
       Result = MsgBox(prompt:="Delete " & Vis & " Name " & _
           Chr(10) & xName.Name & "?" & Chr(10) & _
           "Which refers to: " & Chr(10) & xName.RefersTo, _
           Buttons:=vbYesNo)

       ' If the result is true, then delete the name.
       If Result = vbYes Then xName.Delete

       ' Loop to the next name.
   Next xName

End Sub

' Module to remove all hidden names on active workbook

Sub ShowAllNames()
Dim n As Name
    
    For Each n In ThisWorkbook.Names
        If n.Visible = False Then n.Visible = True
    Next n
End Sub



Sub UpdateRegimeImport_2()

    Application.Calculation = xlManual
    
    countryTabColor = 65535
    Dim sName As String
    Dim col As Integer
    
    ' Clear the previous regime imports
    Sheets("Multi Scenario").Range("E13:AH49").ClearContents
    col = Range("E1").Column
    
    For Each sheet In Sheets
        If sheet.Tab.Color = countryTabColor Then
            Debug.Print "Regime import for " & sheet.Name
            Call LinkCountryRegimeImport_2(sheet.Name, col)
            col = col + 1
        End If
    Next sheet
    Application.Calculation = xlSemiautomatic
    
    Call UpdateMultiScenarioLinks
End Sub



Sub LinkCountryRegimeImport_2(sheetName As String, col As Integer)
    Dim row As Integer
    Dim rangeSrc As String
    
    row = SearchOutputDataStart(sheetName)
    
    If row > 0 Then
    
        'with direct Links
        
        For i = 0 To 37
            Sheets("Multi Scenario").Cells(12 + i, col).Formula = "='" & sheetName & "'!C" & (row + i)
        Next i
        
        'With Arrays
        
        'rangeSrc = "={'" & sheetName & "'!C" & row & ":C" & (row + 38) & "}"
        'Sheets("RegimeImport").Range(Cells(5, col + 5), Cells(row + 37, col + 5)).FormulaArray = rangeSrc
        'Else
        
    End If
    
End Sub


'''''''''''''''''''''''''''''''''''''''''''''''
' module to create references in MultiScenario'
'''''''''''''''''''''''''''''''''''''''''''''''

Sub UpdateMultiScenarioLinks()
    
    Application.Calculation = xlManual
    
    govRevTxt = "Government revenues Positions"
    resTxt = "Results Positions"
    col = 5
    
    Sheets("Multi Scenario").Range("E72:AH115").ClearContents
    
    For Each sh In Sheets
        If sh.Tab.Color Then
        
            rowGovRev = FindRowInSheet(sh, 4, govRevTxt)
            If rowGovRev > 0 Then
                Debug.Print "Copy col: " & col & ", page: " & sh.Name
                Call CopyGovRevenuesPositions(sh, col, rowGovRev + 2)
                Call CopyGovRevenueNames(sh, col, rowGovRev + 2)
            End If
            
            rowGovRes = FindRowInSheet(sh, 4, resTxt)
            If rowGovRes > 0 Then
                Call CopyResultsPositions(sh, col, rowGovRes + 2)
                Call CopyResultsNames(sh, col, rowGovRes + 2)
            End If
            
            col = col + 1
        End If
    Next sh
    
    Application.Calculation = xlSemiautomatic
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''
' module to create references in Result       '
'''''''''''''''''''''''''''''''''''''''''''''''

Sub UpdateMultiScenarioLinks_result()
    
    Application.Calculation = xlManual
    
    govRevTxt = "Government revenues Positions"
    
    resTxt = "Results Positions"
    col = 5
    
    Sheets("Result").Range("E72:AH115").ClearContents
    
    For Each sh In Sheets
        If sh.Tab.Color Then
        
            rowGovRev = FindRowInSheet(sh, 4, govRevTxt)
            If rowGovRev > 0 Then
                Debug.Print "Copy col: " & col & ", page: " & sh.Name
                Call CopyGovRevenuesPositionsResult(sh, col, rowGovRev + 2)
                Call CopyGovRevenueNamesResult(sh, col, rowGovRev + 2)
            End If
            
            rowGovRes = FindRowInSheet(sh, 4, resTxt)
            If rowGovRes > 0 Then
                Call CopyResultsPositionsResult(sh, col, rowGovRes + 2)
                Call CopyResultsNamesResult(sh, col, rowGovRes + 2)
            End If
            
            col = col + 1
        End If
    Next sh
    
    Application.Calculation = xlSemiautomatic
End Sub


' Copy Gov Revenues Positions
' Receives sh Sheet, col Integer, row Integer
Sub CopyGovRevenuesPositions(sh, col, row)
    colPos = 3
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Multi Scenario").Cells(72 + i, col).Value = sh.Cells(row + i, 3).Value
        End If
    Next i
End Sub

' Copy Gov Revenues Positions
' Receives sh Sheet, col Integer, row Integer
Sub CopyGovRevenuesPositionsResult(sh, col, row)
    colPos = 3
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Result").Cells(72 + i, col).Value = sh.Cells(row + i, 3).Value
        End If
    Next i
End Sub

' Copy Gov Revenues Names
Sub CopyGovRevenueNames(sh, col, row)
    colPos = 4
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Multi Scenario").Cells(85 + i, col) = sh.Cells(row + i, colPos).Text
        End If
    Next i
End Sub
' Copy Gov Revenues Names
Sub CopyGovRevenueNamesResult(sh, col, row)
    colPos = 4
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Result").Cells(85 + i, col) = sh.Cells(row + i, colPos).Text
        End If
    Next i
End Sub

' Copy ResutlsPostions in Multi Scenario

Sub CopyResultsPositions(sh, col, row)
    colPos = 3
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Multi Scenario").Cells(98 + i, col).Value = sh.Cells(row + i, colPos).Value
        End If
    Next i
End Sub

' Copy ResutlsPostions in Result

Sub CopyResultsPositionsResult(sh, col, row)
    colPos = 3
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Result").Cells(98 + i, col).Value = sh.Cells(row + i, colPos).Value
        End If
    Next i
End Sub

' Copy ResutlsPostions in Multi Scenario

Sub CopyResultsNames(sh, col, row)
    colPos = 4
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Multi Scenario").Cells(107 + i, col).Value = sh.Cells(row + i, colPos).Value
        End If
    Next i
End Sub

' Copy ResutlsPostions in Result

Sub CopyResultsNamesResult(sh, col, row)
    colPos = 4
    For i = 0 To 15
        If sh.Cells(row + i, colPos).Text = "" Then
            i = 15
        Else
            Sheets("Result").Cells(107 + i, col).Value = sh.Cells(row + i, colPos).Value
        End If
    Next i
End Sub

' Returns the row in which a value is found
' sheet Sheet, col Integer, val String
Function FindRowInSheet(sheet, col, val) As Integer
    Dim i As Integer
    
    found = False
    
    For i = 1 To 1200
        If LCase(sheet.Cells(i, col).Text) = LCase(val) Then
            FindRowInSheet = i
            found = True
            i = 1200
        End If
    Next i
    
    If Not found Then
        FindRowInSheet = 0
    End If
End Function