Loop increments when cell is empty?

[silentwolf]

Hi guys,

I am not able to work this one out...

Following code is working fine when I am transfering a recordset into excel when there is already a record in excel.
But what I like to have is when there is no record in the first column should start the number 1 and after each loop adds 1 to it

Sub ReportToExcel()
    Dim appExcel As Excel.Application
    Dim wbkExcel As Excel.Workbook
    Dim wksExcel As Excel.Worksheet
    Dim rngExcel As Excel.Range
    Dim rcsM As Recordset
    Dim lngZeile As Long
    Dim lngZaehler As Long
    
    Set appExcel = Excel.Application
    Set wbkExcel = appExcel.Workbooks.Open(CurrentProject.Path & "\StundenEintrag.xlsx", , , , , , , , , , , , False)
    appExcel.Visible = True
    
    Set wksExcel = wbkExcel.Worksheets("Rechnungsdaten")
    Set rngExcel = wksExcel.UsedRange
    lngZeile = rngExcel.Rows.Count + rngExcel.Row - 1
    
    Set rcsM = CurrentDb.OpenRecordset("SELECT * FROM tblKontakte WHERE Ort <> 'Kössen'", dbOpenDynaset)
    lngZaehler = lngZeile + 1
    
    Do Until rcsM.EOF
        wksExcel.Cells(lngZaehler, 2).Value = rcsM.Fields("Ort").Value
        
            wksExcel.Cells(lngZaehler, 1).Value = wksExcel.Cells(lngZaehler - 1, 1).Value + 1


        rcsM.MoveNext
        lngZaehler = lngZaehler + 1
        
    
    Loop
    
End Sub

So if a record is already in the excel worksheet every other record will be added by +1 in column A ..
But what or how does this code has to be changed so it wont matter if there is a record or not?

I tried with an if statement but without luck..
Would be nice if someone could help me please..


Many thanks as always!

Silentwolf

 

[Heder_Santos]

Hello SilentWolf,

I don't know if I got your question but, have you tried to use a "For/Next" loop for example?

You could use a variable as a row counter, and after each iteration, it'll be increased by 1. For example

Dim dblRowCounter as Double
Dim dblFinalRow as Double

For dblRowCounter = 1 to dblFinalRow

    (Your loop commands in here)

Next dblRowCounter

Maybe it'll solve your issue, as I only have seen you mentioning a "Do" Loop, and an "If" statement.

Regards!

Heder

 

[silentwolf]

Hi Heder,

thanks for your reply!
I will try with your solution ,.. but will try to clarify my above code or what I like to achive better.
In the above code it finds the last row of cells regardless where it would start in the sheet so not particulary ("A1") ... it works fine..
I got in column A the ID so the code looks currently puts data into column "B"... and looks for the ID in the column A but in one row above and increment that number by one..

So my question is. If there is no data in one row above then start in the first emply row in column "A" with one and then inrease each line by +1 in column A..


Maybe it is clear or better clear what I am after?

Currently the code works fine if there is a row of data existing but if not??
I really would like the code to be kept as it is but with this small... imporvement as mentioned.

Many thanks

Silentwolf