VBA Problem

[jamescooper]

Trying to get the result of what dow1 equals to enter in the later line code code, so it will either be F12, G12 or H12.

Cannot quite get it to work - any ideas please?

    If (Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("B6") = "Sunday") Then
    dow1 = "H"
    If (Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("B6") = "Saturday") Then
    dow1 = "G"
    Else: dow1 = "F"

    destcell1.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", ("dow1" & "12"))

 

[sheetspread]

You don't need the open parentheses with the If statements nor the semicolon after Else

dow1 and 12 shouldn't need quotes

 

[jamescooper]

You don't need the open parentheses with the If statements nor the semicolon after Else

dow1 and 12 shouldn't need quotes

Thanks, have tried to incorporate - cannot quite get it to work:

    If Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("B6") = "Sunday" Then
    dow1 = H
    If Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("B6") = "Saturday" Then
    dow1 = G

    destcell1.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", dow1 & 12)

Any ideas please?

 

[sheetspread]

The H and F do need quotes

You removed the else, which might be ok, but if "F" is the default just declare it before the If

 

[jamescooper]

Maybe I should just copy all the code and show you what I am trying to do:

So whatever is in Range ("B6"), is there either an F, G or H which is put into the getcellvalue coding...

Sub Forecast_Day_1()

    Dim destcell As Range, r As Long
    Dim fileSpec As String, folderPath As String, fileName As String
    Dim FilePath As String
    Dim TestStr As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim FP As String, FN As String
    Dim dow1 As Variant
    
    Dim Found As Boolean
    Dim z As Byte
    Dim Items() As String


    Items = Split("1710,1711,1712,1713,1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,1912,1913,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2101,2102,2103,2104,2105,2106,2107,2108,2109,2110,2111,2112,2113,2201,2202,2203,2204,2205,2206,2207,2208,2209,2210,2211,2212,2213,2301,2302,2303,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2401,2402,2403,2404,2405,2406,2407,2408,2409,2410,2411,2412,2413,2501,2502,2503,2504,2505,2506,2507,2508,2509,2510,2511,2512,2513,2601,2602,2603,2604,2605,2607,2608,2609,2610,2611,2612,2613,2701,2702,2703,2704,2705,2706,2707,2708,2709,2710,2711,2712,2713,2801,2802,2803,2804,2805,2806,2807,2808,2809,2810,2811,2812,2813,2901,2902,2903,2904,2905,2906,2907,2908,2909,2910,2911,2912,2913,3001,3002,3003,3004,3005,3006,3007,3008,3009,3010,3011,3012,3013", ",")
    Found = False
    Do
    myValue3 = InputBox("Enter the railway period, e.g. 1804")
    If StrPtr(myValue3) = 0 Then Exit Sub
    For z = LBound(Items) To UBound(Items)
        If myValue3 = Items(z) Then Found = True
    Next
    Loop While Not Found


    Dim NoFile As Boolean
    NoFiles = False
       
    FD = Workbooks("Schedule 4 Model.xlsm").Worksheets("Schedule 4 Model").Range("Z1")
        
    Workbooks.Open fileName:= _
    FD & "Forecast\Forecast Template.xlsx"


    Sheets("Summary").Range("C3").Value = myValue3
    
    cellvalue1 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("U6")
    
    If Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("B6") = "Sunday" Then
    dow1 = "H"
    If Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("B6") = "Saturday" Then
    dow1 = "G"
    If Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("B6") = "Monday" Or "Tuesday" Or "Wednesday" Or "Thursday" Or "Friday" Then
    dow1 = "F"
       
    FilePath = FD & "Revenue\" & myValue3 & "\" & cellvalue1
    
    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
    Call Forecast_Day_2
    Else
    
    fileSpec = FD & "Revenue\" & myValue3 & "\" & cellvalue1 & ""
       
    Set destcell1 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("C6")
    Set destcell2 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("D6")
    Set destcell3 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("E6")
    Set destcell4 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("F6")
    Set destcell5 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("G6")
    Set destcell6 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("H6")
    Set destcell7 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("I6")
    Set destcell8 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("J6")
    Set destcell9 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("K6")
    Set destcell10 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("L6")
    Set destcell11 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("M6")
    Set destcell12 = Workbooks("Forecast Template.xlsx").Sheets("Revenue").Range("N6")
    
        r = 0
    
    folderPath = Left(fileSpec, InStrRev(fileSpec, "\"))
    
    fileName = Dir(fileSpec)
    
    While Len(fileName) <> 0
    
    destcell1.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", dow1 & 12)
    destcell2.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H20")
    destcell3.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H28")
    destcell4.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H37")
    destcell5.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H48")
    destcell6.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H55")
    destcell7.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H64")
    destcell8.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H72")
    destcell9.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H80")
    destcell10.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H87")
    destcell11.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H96")
    destcell12.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H105")


        r = r + 1
        
        fileName = Dir
    
    Wend
    
Call Forecast_Day_2


    End If
     
    End If
              
End Sub