none
trying to set condition if any cells in the range are empty RRS feed

  • Question

  • I am trying to check a range of cells c2 to c17 if they are empty or not if they are empty i would like to show the message and if not i would like to proceed. 

    any help is welcome.

    Kosta 

    Sub Save_Click()
    Dim wks As Worksheet
        Dim AddNew As Range
        Dim imce As Range
        Set wks = Sheet1
        Set AddNew = wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1, 0) 'Finding the next empty row
        Set imce = Range("C2:C17")


        imce.Select
        If Application.CountA(Range(imce)) = 0 Then
        MsgBox "Nothing to Save"
        GoTo endsub:
        Else

            AddNew.Value = AddNew.Offset(-1, 0).Value + 1 'Entering the next ID #
            'AddNew.Offset(0, 0).Value = TextCompanyNumber.Text
            'AddNew.Offset(0, 20).Value = Cells(2, 3).Value 'Company #
            AddNew.Offset(0, 3).Value = Cells(3, 3).Value 'Company Name
            AddNew.Offset(0, 4).Value = Cells(4, 3).Value 'Address 1
            AddNew.Offset(0, 20).Value = Cells(5, 3).Value 'Address 2
            AddNew.Offset(0, 5).Value = Cells(6, 3).Value 'City
            AddNew.Offset(0, 6).Value = Cells(7, 3).Value 'State
            AddNew.Offset(0, 7).Value = Cells(8, 3).Value 'Zip
            'AddNew.Offset(0, 21).Value = Cells(9, 3).Value 'Zip 4
            AddNew.Offset(0, 14).Value = Cells(10, 3).Value 'Telephone
            AddNew.Offset(0, 16).Value = Cells(11, 3).Value 'Fax
            AddNew.Offset(0, 17).Value = Cells(12, 3).Value 'Email Address
            AddNew.Offset(0, 30).Value = Cells(13, 3).Value 'Company Code
            AddNew.Offset(0, 26).Value = Cells(14, 3).Value 'Tax ID.
            AddNew.Offset(0, 27).Value = Cells(15, 3).Value 'Creditcard #
            AddNew.Offset(0, 28).Value = Cells(16, 3).Value 'Creditcard Date
            AddNew.Offset(0, 29).Value = Cells(17, 3).Value 'Creditcard Code
            Range("C2:C17").Clear

        End If
    endsub:
    End Sub

                                    

    Vforum

    Monday, October 14, 2019 2:24 PM

Answers

  • Hi Kosta si,

    Please do:
    1) Name the range C2:C17 as "imce2" in Name Box.
        
    2) Modify code like
            If WorksheetFunction.CountA(Range("imce2")) = 0 Then
            MsgBox "Nothing to Save"
            GoTo endsub:

    Regards,

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Monday, October 14, 2019 11:37 PM
    • Marked as answer by Kosta si Tuesday, October 15, 2019 12:21 AM
    Monday, October 14, 2019 11:35 PM

All replies

  • Hi Kosta si,

    Please do:
    1) Name the range C2:C17 as "imce2" in Name Box.
        
    2) Modify code like
            If WorksheetFunction.CountA(Range("imce2")) = 0 Then
            MsgBox "Nothing to Save"
            GoTo endsub:

    Regards,

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Monday, October 14, 2019 11:37 PM
    • Marked as answer by Kosta si Tuesday, October 15, 2019 12:21 AM
    Monday, October 14, 2019 11:35 PM
  • thank you,

    it's so many way to achieve the same results.

    Thank you again.

    Kosta 


    Vforum

    Tuesday, October 15, 2019 12:21 AM