none
VBA Code Counta RRS feed

  • Question

  • Hi folks,

    I am using this code to have and I am expecting to have the last row added as a Serial number (1,2,3,4, etc.) but without success as my new entry overwrites the previous one instead can you help?

    Dim sh As Worksheet

        Dim iRow As Long
     
        Set sh = ThisWorkbook.Sheets("Sheet3")
        
        iRow = [Counta(Sheet3!A:A)] + 1
        
            With sh
            
            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = Cruisebook.ComboBoxStatus.Value
            .Cells(iRow, 3) = Cruisebook.txtDuration.Value
            .Cells(iRow, 4) = Cruisebook.cmbday.Value
            .Cells(iRow, 5) = Cruisebook.cmbmonth.Value
            .Cells(iRow, 6) = Cruisebook.cmbyear.Value

    Thanks


    Tuesday, March 24, 2020 3:09 AM

Answers

  • The problem is that there are data in rows 1048571 to 1048576. If you clear these rows, the following should work correctly:

    Sub Submit()
        Dim sh As Worksheet
        Dim iRow As Long
     
        Set sh = ThisWorkbook.Sheets("CRUISE BOOKING DASHBOARD")
        With sh
            iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = Cruisebook.ComboBoxStatus.Value
            .Cells(iRow, 3) = Cruisebook.txtDuration.Value
            .Cells(iRow, 4) = Cruisebook.cmbday.Value
            .Cells(iRow, 5) = Cruisebook.cmbmonth.Value
            .Cells(iRow, 6) = Cruisebook.cmbyear.Value
            .Cells(iRow, 8) = [Text(Now()+7,"DD-MMMM")]
            .Cells(iRow, 9) = Cruisebook.txtRevenue.Value
            .Cells(iRow, 10) = [Counta(ACCOUNTING STATUS!Range("D1:D90").Select)]
            .Cells(iRow, 11) = Cruisebook.agtName.Value
            .Cells(iRow, 12) = Cruisebook.txtpaxNbr.Value
            .Cells(iRow, 13) = Cruisebook.lstBkgtype.Value
            .Cells(iRow, 14) = Cruisebook.lstDestination.Value
            .Cells(iRow, 15) = Cruisebook.lstBkgchannel.Value
            .Cells(iRow, 16) = Cruisebook.lstCountry.Value
            .Cells(iRow, 17) = Cruisebook.lstCruiselines.Value
            .Cells(iRow, 18) = [IF('CRUISE BOOKING DASHBOARD'!B2:B30000 ="BK",'CRUISE BOOKING DASHBOARD'!H2:H30000+7,"-")]
            .Cells(iRow, 19) = [Text(Now(), "DDD-D-MMM-YYYY HH:MM:SS")]
            .Cells(iRow, 20) = Application.UserName
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Giacomo Moreno Wednesday, March 25, 2020 1:39 PM
    Wednesday, March 25, 2020 12:56 PM

All replies

  • Try another expression:

    iRow = IIf(WorksheetFunction.CountA(sh.UsedRange) = 0, 1, sh.UsedRange.Row + sh.UsedRange.Rows.Count)


    • Edited by Viorel_MVP Tuesday, March 24, 2020 8:52 AM
    Tuesday, March 24, 2020 8:52 AM
  • Thanks Viorel, I tried this ine of code but it does not work. I am wanting to have the next row filled with the new data but it keeps on overwritting the current line still, do you see any alternative?
    Tuesday, March 24, 2020 11:22 AM
  • Change

        iRow = [Counta(Sheet3!A:A)] + 1

    to

        iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 24, 2020 12:14 PM
  • Still not working Hans I get a invalid or unqulified reference :s

    could I combine both codes somehow?

    Tuesday, March 24, 2020 12:37 PM
  • I forgot to mention that the modified line should be below the line With sh:

        Dim sh As Worksheet
        Dim iRow As Long
     
        Set sh = ThisWorkbook.Sheets("Sheet3")
        With sh
            iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = Cruisebook.ComboBoxStatus.Value
            .Cells(iRow, 3) = Cruisebook.txtDuration.Value
            .Cells(iRow, 4) = Cruisebook.cmbday.Value
            .Cells(iRow, 5) = Cruisebook.cmbmonth.Value
            .Cells(iRow, 6) = Cruisebook.cmbyear.Value
        End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 24, 2020 1:15 PM
  • hey thanks, this still does not work, i am using excel 365 not sure if that can be a reason
    Tuesday, March 24, 2020 11:04 PM
  • It shouldn't mattter which version of Excel you use.

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 25, 2020 8:30 AM
  • Hey Hans nothing too sensitive here please see https://we.tl/t-shNVNHubfl

    thanks a lot

    Wednesday, March 25, 2020 12:30 PM
  • The problem is that there are data in rows 1048571 to 1048576. If you clear these rows, the following should work correctly:

    Sub Submit()
        Dim sh As Worksheet
        Dim iRow As Long
     
        Set sh = ThisWorkbook.Sheets("CRUISE BOOKING DASHBOARD")
        With sh
            iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = Cruisebook.ComboBoxStatus.Value
            .Cells(iRow, 3) = Cruisebook.txtDuration.Value
            .Cells(iRow, 4) = Cruisebook.cmbday.Value
            .Cells(iRow, 5) = Cruisebook.cmbmonth.Value
            .Cells(iRow, 6) = Cruisebook.cmbyear.Value
            .Cells(iRow, 8) = [Text(Now()+7,"DD-MMMM")]
            .Cells(iRow, 9) = Cruisebook.txtRevenue.Value
            .Cells(iRow, 10) = [Counta(ACCOUNTING STATUS!Range("D1:D90").Select)]
            .Cells(iRow, 11) = Cruisebook.agtName.Value
            .Cells(iRow, 12) = Cruisebook.txtpaxNbr.Value
            .Cells(iRow, 13) = Cruisebook.lstBkgtype.Value
            .Cells(iRow, 14) = Cruisebook.lstDestination.Value
            .Cells(iRow, 15) = Cruisebook.lstBkgchannel.Value
            .Cells(iRow, 16) = Cruisebook.lstCountry.Value
            .Cells(iRow, 17) = Cruisebook.lstCruiselines.Value
            .Cells(iRow, 18) = [IF('CRUISE BOOKING DASHBOARD'!B2:B30000 ="BK",'CRUISE BOOKING DASHBOARD'!H2:H30000+7,"-")]
            .Cells(iRow, 19) = [Text(Now(), "DDD-D-MMM-YYYY HH:MM:SS")]
            .Cells(iRow, 20) = Application.UserName
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Giacomo Moreno Wednesday, March 25, 2020 1:39 PM
    Wednesday, March 25, 2020 12:56 PM
  • It worked! thanks a million God bless!
    Wednesday, March 25, 2020 1:39 PM
  • Hi Hans, hope you are keeping safe!

    Got another topic it seems easy but it has been blowing my mind for the past days... I have posted on the forum bt so far no answer (itrelates to SAVE and RESET COMMAND BUTTONS in my code, they are crashing Excel and I cannot figure out why... could you help please?

    I've seen your community page not sure if it would be the right platform to exchange more frequently but anyways hope to hear from you...

    Sunday, April 5, 2020 7:49 PM