How to make loop stop when the cell is empty? (VBA)

[feni1388]

Hello....

Perhaps some of the code that I'm running is incorrect, but the loop that I made didn't stop when the cell is empty --> cell (i, 29)
Can you someone please help?

 Dim sFolderPath As String
    Dim sFileName As String, sFilePath As String
    Dim wbName As String
    
    
    'Define Folder Path
    sFolderPath = "\\192.168.1.101\Share\admin\planet\"
      
            
      
    'Check Specified File exists or not
    
    For i = 2 To 8
        wbName = "planetupload" & Cells(i, 29).Value & ".xlsm"
    
    sFilePath = sFolderPath & wbName
    
    
    If Dir(sFilePath) <> "" Then
        Workbooks.Open Filename:="\\192.168.1.101\Share\admin\planet\" & wbName
        
        ThisWorkbook.Activate
        Sheets("futuredate").Select
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False

        ActiveWorkbook.Close (True)
        
        
    Else
    
    'If file is not available
    
    Workbooks.Open Filename:="\\192.168.1.101\Share\admin\planet\planetupload" & ".xlsm"
    
    ActiveWorkbook.SaveAs sFolderPath & wbName
    
       Workbooks("planetupload" & Format(Date, "yyyymmdd") & ".xlsm").Activate
        Sheets("copy").Select
        If Not Sheets("copy").AutoFilterMode Then Range("A1:Z1").AutoFilter
        ActiveSheet.Range("$A$1:$Y$501").AutoFilter Field:=26, Criteria1:=Cells(i, 29)
        Columns("A:Y").Select
        Selection.Copy
        
        Workbooks(wbName).Activate
        Sheets("futuredate").Select
        Columns("A:Y").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
      
      
      
    
    
    End If
    
    Next i
    
    

    MsgBox "Future date template is complete"



ActiveWorkbook.Save

 

[kvsrinivasamurthy]

Change this line

For i = 2 To 8
 wbName = "planetupload" & Cells(i, 29).Value & ".xlsm"

As

For i = 2 To 8
If Cells(i, 29)<>"" then
 wbName = "planetupload" & Cells(i, 29).Value & ".xlsm"
....full code
end if
Next i

 

[feni1388]

Change this line

For i = 2 To 8
 wbName = "planetupload" & Cells(i, 29).Value & ".xlsm"

As

For i = 2 To 8
If Cells(i, 29)<>"" then
 wbName = "planetupload" & Cells(i, 29).Value & ".xlsm"
....full code
end if
Next i

Thank you.... It works perfectly OK.

 

[kvsrinivasamurthy]

Welcome.