• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro is not working

delta

Member
Sheet name " Ledger "
contian

Col A : Party Names
Col B : Dates
Col C : Bill no
Col D : Bill Amount

userform name "Account_Ledger"

Comobbox1 = Party name
Textbox2 = Date
Textbox3 = Bill no
Textbox4 = Bill Amount

i would like when end date and party name is same then nothing , otherwise above macro is run
but given macor is add same end date and same party name.
why only second row of Sheet " Ledger " name e.g.(KALPESH PATEL) is not repeate



when i click ADD button then Below macro is run

>>> use code - tags <<<
Code:
Sub Add_Data()


Dim lastrow As Integer
Dim i As Integer

    lastrow = Sheets("Ledger").Cells(Rows.Count, 1).End(xlUp).Row + 1
      
    With Account_Ledger
  
                For i = 2 To lastrow

If Sheets("Ledger").Cells(i, 2).Value = CDate(.TextBox2.Value) And Sheets("Ledger").Cells(i, 1).Value = .ComboBox1.Text Then

       Exit Sub
       Else

        Sheets("Ledger").Cells(lastrow, 1) = .ComboBox1.Value                                    ' PARTY NAME
        Sheets("Ledger").Cells(lastrow, 2) = CDate(.TextBox2.Value)                              ' BILL DATE
        Sheets("Ledger").Cells(lastrow, 2).NumberFormat = ("dd/mm/yyyy")                         ' BILL DATE

        Sheets("Ledger").Cells(lastrow, 3) = .TextBox3.Value                                     ' BILL NO
        Sheets("Ledger").Cells(lastrow, 4) = Val(.TextBox4.Value)                                ' BILL AMOUNT

End If
                Next i
End With

End Sub
plse help

i attache sample file
 

Attachments

  • Test.xlsm
    24.1 KB · Views: 7
Last edited by a moderator:
Have a try with these modifications to your macro:
Code:
Option Explicit
Sub Add_Data()
    Dim lastrow As Integer
    Dim i      As Integer
    lastrow = Sheets("Ledger").Cells(Rows.Count, 1).End(xlUp).Row + 1
    With Account_Ledger
        For i = 2 To lastrow
            If Sheets("Ledger").Cells(i, 2).Value = CDate(.TextBox2.Value) _
               And Sheets("Ledger").Cells(i, 1).Value = .ComboBox1.Text Then
                MsgBox "Party/Date already present"     '<- added
                Exit Sub
            End If     '<- moved up
        Next i     '<-  moved up
        Sheets("Ledger").Cells(lastrow, 1) = .ComboBox1.Value ' PARTY NAME
        Sheets("Ledger").Cells(lastrow, 2) = CDate(.TextBox2.Value) ' BILL DATE
        Sheets("Ledger").Cells(lastrow, 2).NumberFormat = ("dd/mm/yyyy") ' BILL DATE
        Sheets("Ledger").Cells(lastrow, 3) = .TextBox3.Value ' BILL NO
        Sheets("Ledger").Cells(lastrow, 4) = Val(.TextBox4.Value) ' BILL AMOUNT
    End With
End Sub
 
Back
Top