Hopefully an easy Input Box data question...

[Belair58]

Hello,

I have a macro that is pulling data from a sheet Cycle_14a_Output.txt. There will be multiple sheets used for this project, all name Cycle_XXa_Output.txt.

I've added an input box to ask what the number of the file should be.

How can I add the Input Data into the formula string?

<Code>

Sub Cycle_Reporting_Output_By_Cycle()
'
' Cycle_Reporting_Output_By_Cycle Macro
'

'
Dim Cycle As Variant

Cycle = InputBox("What Cycle Number and Letter?")


Range("E2").Select
Selection.Copy
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F6").Select
ActiveWindow.SmallScroll Down:=-70
Range("E1").Select
ActiveCell.FormulaR1C1 = "LATE / OnTIME"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="""","""",IFERROR(VLOOKUP(RC[-4],Cycle_14a_Output.txt!C1,On-Time,FALSE),""LATE""))"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E69"), Type:=xlFillDefault
Range("E2:E69").Select
ActiveWindow.SmallScroll Down:=-70
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="""","""",IFERROR(VLOOKUP(RC[-4],Cycle_14a_Output.txt!C1,1,FALSE),""LATE""))"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E69"), Type:=xlFillDefault
Range("E2:E69").Select
ActiveWindow.SmallScroll Down:=-50
Range("F1").Select
ActiveCell.FormulaR1C1 = "LATE / On-Time"
Columns("F:F").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-10
End Sub

</Code>

Thanks for any help,

 

[Fluff]

How about

"=IF(RC[-4]="""","""",IFERROR(VLOOKUP(RC[-4],Cycle_" & cycle & "_Output.txt!C1,On-Time,FALSE),""LATE""))"