[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!