+ Reply to Thread
Results 1 to 3 of 3

Starting poing for loop not static

Hybrid View

oakman Starting poing for loop not... 06-25-2007, 10:58 AM
royUK Where did you name it? In the... 06-25-2007, 11:54 AM
oakman Hello royUK, I actually... 06-25-2007, 12:24 PM
  1. #1
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117

    Starting poing for loop not static

    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

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Where did you name it? In the code or as a nmaed range using the Name box
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117
    Hello royUK,
    I actually named Cell E19 in Sheet 4 using the Name box. The problem is that if the data starts in row E20, my loop will not run. Or if my data starts befor row 19, I will miss the data before cell E19. Does this answer your question?

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1