hello to all and thank you in advance for the help that may be provided. The code below works very well - and i would not have created it without the help of some very kind people in this forum - but what i just found out is that the point at which i named my range "inTbl" may not always be the same. Thus, when the DO While loop starts, if my data comes in a row short, there is a blank and the loop does not run, also if my data comes in with a few rows extra, i may miss some data. Any suggestions are very much appreciated.
Sub Get_data
'
Dim Contract As Variant
Dim Flow As Double
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisFile = ThisWorkbook.Name
Contract = Sheets(1).Range("C22:C36")
PathName = Sheets(1).Range("C3").Value
Filename = Sheets(1).Range("C6").Value
'Copy CSV with RT flow from nMarket
Workbooks.Open Filename:=PathName & Filename
Workbooks(Filename).ActiveSheet.Cells.Copy _
Workbooks(ThisFile).Sheets(3).Range("A1")
Workbooks(Filename).Close
'Update Day Ahead flow in the Flow spreadsheet
Sheets(3).Activate
i = 0
Do While Len(Range("inTblRT").Offset(i, 0).Value) > 0
If Range("inTblRT").Offset(i, 2).Value = "HAM" Then
MarketOffset = 1
Else
MarketOffset = 0
End If
'Update each contract in the DA-RT FLOW sheet
'If there is a need to increase Contracts in the sheet and the code,_
'simply change the number below For j from 1 to the number that may be needed
For j = 1 To 13
If Range("Contract" & j).Value = Range("inTbl").Offset(i, 0).Value Then
Range("Contract" & j).Offset(Hour(Range("inTbl").Offset(i, 3).Value) + 3, MarketOffset).Value = _
Range("inTbl").Offset(i, 4).Value
Exit For
End If
Next j
i = i + 1
Loop
Sheets("TRANS SPECS").Activate
Range("B34").Select
Application.ScreenUpdating = True
End Sub
Bookmarks