I have a sub that currently looks for a cell containing the word "Effective Date" which is basically a column header for a list that I'm looping through (which happens to start at cell E24). I want to grab the current position i'm at to start my loop instead of hardcoding i = 24 to 200 (so 24 should be the current cell). How do I do this? The below is my loop

' Find beginning of list of specification files
Range("A1").Select
    Cells.Find(What:="Effective Date", After:=ActiveCell, LookIn:=xlFormulas2 _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(2, 0).Select
        
 
 Dim i
 Dim Folder

    For i = 24 To 200
      If IsEmpty(Range("E" & i).Value) Or IsEmpty(Range("G" & i).Value) Then
      ' do nothing
      Else
            Range("G" & i).Select
            Folder = ActiveCell
        Call ListFilesInFolder(Folder, DestinationLoc)
        End If
    Next i
    

    
End Sub

CodePudding user response：

It is not entirely clear what you are trying to do, I have updated the code to be a bit more efficient and added some comments/questions.

'You should define what your variable types are
Dim oFound As Object
Dim i As Long
Dim Folder As Range
Dim DestinationLoc 'as ????

'Find beginning of list of specification files
'Range("A1").Select  'Select as few things as possible - that takes time

'
Set oFound = ActiveSheet.UsedRange.Cells.Find(What:="Effective Date", LookIn:=xlFormulas2, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)  'Probably should use xlWhole instaaed of xlPart
If Not oFound Is Nothing Then
    'Found a cell containing the searched for value
    'ofound is a range that can be accessed to get any info about that range
    'ActiveCell.Offset(2, 0).Select   why select this cell??
    'oFound.Row   1 is the row below "Effective Date"
    
    
    For i = oFound.Row   1 To 200   'Where does 200 come from?
        If IsEmpty(Range("E" & i).Value) Or IsEmpty(Range("G" & i).Value) Then
            ' do nothing
        Else
            'Range("G" & i).Select
            'Folder = ActiveCell
            'Replace the above 2 lines iwth
            Folder = Range("G" & i).Value
            
            Call ListFilesInFolder(Folder, DestinationLoc)   'where does DestinationLoc value come from?
        End If
    Next i

Else
        MsgBox "'Effective Date' not found"

End If