[Solved] Alternative to MoveFirst (not MoveNext)

[JoeKerr99]

Hi, A little question....

I have a Macro running at it reads data from a CSV file onto my spreadsheet.

The first 'If' The first record on the list needs to be the last one on my spreadsheet and so on. I can set the number of records I want to load at once (say 10) and they load no problem with MoveFirst.

I have a second 'If' statement that moves this data table to the right (so from cell K2 to L2 and so on)and I WANT IT TO read the next row on the CSV (say row 11) and places it in the K column and ccary on. The problem is, moveFirst only takes the first record, MoveNext only takes the 2nd. I want to go from the last record (in this example 10) and at the 11th (so now I should have record 11 in k, 10 in L and so on)

Perhaps easier phrased: Is there a way to add to the MoveFirst start value??

I attached a sample csv file too

Sub Button4_Click()

Dim sht As Worksheet
Dim rs As Recordset
Dim NoOfPrices, firstPriceCol, x, i, a, LastRow, rowNum, colNum, intVal, colNumVal As Integer
Dim curr, coin, filePath, filename, strcon, strSQL As String
Dim errDtl
Dim rangeToMove As Range
On Error GoTo errDtl

Application.ScreenUpdating = False
Application.EnableEvents = False

'Set sheets name
Set sht = Sheets(1)

'Read currency format, Price Columns, First Price Column from Sheet
filePath = sht.Range("folderPath").Value
NoOfPrices = sht.Range("NoOfPrices").Value
firstPriceCol = sht.Range("firstPriceCol").Value
intVal = sht.Range("intVal").Value
colNumVal = sht.Range("colNumVal").Value


If filePath = "" Then
    sht.Range("message").Value = "Kindly provide Folder Path, ending with back slash '\'"
    sht.Range("folderPath").Activate
    Exit Sub
ElseIf NoOfPrices = "" Then
    sht.Range("message").Value = "Please enter, how many price columns needed for each coins"
    sht.Range("NoOfPrices").Activate
    Exit Sub
ElseIf IsEmpty(firstPriceCol) Then
    sht.Range("message").Value = "Please enter first column number to set Price (more then 9) "
    sht.Range("firstPriceCol").Activate
    Exit Sub
End If

If firstPriceCol < 10 Then
    sht.Range("message").Value = "First Price column number should be more then 9"
    sht.Range("firstPriceCol") = ""
    Exit Sub
End If



'Count Symbols from 'C' Column
LastRow = sht.Range("E8000").End(xlUp).row

'If no symbols provided in sheets
If LastRow < 3 Or IsEmpty(sht.Range("E3")) Then
    sht.Range("message").Value = "Kindly provide symbol/coin list in 'E3 and onwards cells'"
    sht.Range("E3").Activate
    Exit Sub
Else

   'set range to move on right side after each interval
    Set rangeToMove = sht.Range(sht.Cells(2, firstPriceCol), sht.Cells(LastRow, (firstPriceCol + NoOfPrices) - 1))

    sht.Range("message").Value = "Processing, Please wait"
    
    
If intVal <> 1 Then
    
 'First of All Write Price Column Heading
For i = 0 To NoOfPrices - 1
    sht.Cells(1, firstPriceCol + i).Value = "Price " & i + 1
Next i
  
  
  For x = 3 To LastRow
        coin = sht.Range("E" & x).Value
        filename = getFileNameFromFolder(filePath, coin)
        
        If Not IsEmpty(filename) Then
        
            Set rs = CreateObject("ADODB.Recordset")
            strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";" _
            & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
            
            strSQL = "SELECT * FROM " & filename
            
            rs.Open strSQL, strcon, 3, 3
            rs.MoveFirst
          
          
            i = 1
            colNum = firstPriceCol + NoOfPrices - 1
            
            For a = 1 To NoOfPrices
                If i <= NoOfPrices Then
                    If rs("time") <> "" Then
                        'sht.Cells(2, colNum) = unixToDate(rs("time"))
                       sht.Cells(x, colNum) = rs("open")
                        i = i + 1
                       colNum = colNum - 1
                       
                   End If
                Else
                    Exit For
                End If
                rs.MoveNext
            Next a
        End If
        
    Next x

Else
 
     Range("K2:BCZ100").Select
    Selection.Cut
      Range("L2").Select
    ActiveSheet.Paste
 
'First of All Write Price Column Heading
For i = 0 To NoOfPrices
    sht.Cells(1, firstPriceCol + i).Value = "Price " & i + 1
Next i
 
 
 
    For x = 3 To LastRow
        coin = sht.Range("E" & x).Value
        filename = getFileNameFromFolder(filePath, coin)
        
        If Not IsEmpty(filename) Then
        
            Set rs = CreateObject("ADODB.Recordset")
            strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";" _
            & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
            
            strSQL = "SELECT * FROM " & filename
            
            rs.Open strSQL, strcon, 3, 3
            rs.MoveFirst
          
          
            'i = 1
            colNum = firstPriceCol
            
           
                
                    If rs("time") <> "" Then
                        sht.Cells(2, colNum) = unixToDate(rs("time"))
                        sht.Cells(x, colNum) = rs("open")
                        
                       
                    End If
               
                
                rs.MoveNext
                
              sht.Range("NoOfPrices").Value = NoOfPrices + intVal
              sht.Range("colNumVal").Value = colNum
           
        End If
        
    Next x
    

            
            
 End If
            
            
            
            
            
            
    'Autofit Columns
    rangeToMove.EntireColumn.AutoFit
    
    sht.Range("message").Value = "Data processed/executed successfully"
    
    Set rs = Nothing
    Set rangeToMove = Nothing
    Set sht = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
End If
errDtl:
    Set rs = Nothing
    Set rangeToMove = Nothing
    Set sht = Nothing
    Sheets(1).Range("message").Value = Err.Description
    Exit Sub
End Sub


Private Function unixToDate(lgnDt) As Date
On Error Resume Next
    unixToDate = Format((lgnDt / 86400 / 1000) + 25569, "DD/MM/YYYY HH:MM:SS")
End Function


Function getFileNameFromFolder(path, filename) As String
Dim MyObj As Object, MySource As Object, file As Variant
 file = Dir(path)
 While (file <> "")
    If InStr(file, LCase(filename)) > 0 Or InStr(file, UCase(filename)) > 0 Then
       getFileNameFromFolder = file
       Exit Function
    End If
   file = Dir
Wend
End Function


Sub Macro3()
'
' Macro3 Macro
'

'

End Sub 

[Artik]

Please edit your post and close the code in [code] tags (select the code and click icon [#] ).

Artik

[JoeKerr99]

Done thanks

[Artik]

I didn't parse the code, but try adding the line after rs.MoveFirst:

Please Login or Register  to view this content.

Artik

[JoeKerr99]

Champion of the WORLD! Thank you SO much!!! That's super awesome!!! Works as I wanted!