I have a very large set of data with specific data in rows that needs formatted and saved as a text file to be used by another program.

I can get it to work for one row but not to repeat for the next row. I am getting a 1004 error.

Here's the code

Sub MakeTxtFile2()

  Dim x As Integer
  Dim FileName As String
  Dim FSO As Object
  Dim FileLoc As String
  Dim Rng As Range
  Dim TxtFile As Object
  Dim WSH As Object
  
    For x = Sheets("Sheet1").Cells(17, 23).Value To Sheets("Sheet1").Cells(18, 23).Value
    FileName = Sheets("Sheet1").Cells(x, 22).Value
    Set WSH = CreateObject("WScript.Shell")
    FileLoc = WSH.SpecialFolders("H:") & "\DSIWM\A QUEEN\GeoRG Manual 2009\Ohio EPA's STRONG MOTION DATABASE\"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set Rng = Sheets("Sheet1").Range(Cells(x, 23), Cells(x, 758))
  
      
      Set TxtFile = FSO.OpenTextFile(FileLoc & FileName, 2, True, -2)
        For Each cell In Rng
          TxtFile.Write Replace(cell, "*", vbCr)
        Next cell
        
      TxtFile.Close
      
    Set WSH = Nothing
    Set FSO = Nothing
    Next x
End Sub