none
excel vba RRS feed

  • Question

  • i have a excel database which include invoice no , cheque data , cheque no , amount .all i want is to get list of cheques below data current date( or spesipic date) in a msgbox.i wrote and script it works well when test one by one.when it loops msg box includes cheque details over the current date.here is sample of my data base and mn vb script
    88725 01.12.14 312520 81820.00
    88726 18.12.14 500611  9900.00

    =======
    script


    Private Sub CommandButton5_Click()
    Dim txt As Integer
    Dim txt2 As Integer
    Dim txt3 As Integer
    Dim str As String
    Dim sht As Worksheet
    Set sht = ActiveSheet
    sht.Range("B3").Activate


    txt = Left(ActiveCell.Formula, 2)
    txt2 = Mid(ActiveCell.Formula, 4, 2)
    txt3 = Mid(ActiveCell.Formula, 7, 2)

    Do Until ActiveCell.Value = ""

    If txt3 >= 15 Then
    ActiveCell.Offset(1, 0).Select
    Else

      If txt > 12 Then
      
      ActiveCell.Offset(1, 0).Select
      Else
         If txt > 15 Then
         ActiveCell.Offset(1, 0).Select
         Else
         str = str + ActiveCell.Offset(0, -1).Text & "    " & ActiveCell.Text & "    " & ActiveCell.Offset(0, 1).Text & "    " & ActiveCell.Offset(0, 2).Text & "    " & vbCrLf
       
         
         End If
         ActiveCell.Offset(1, 0).Select
      End If
    End If
    Loop

    MsgBox str

    End Sub
    Tuesday, December 16, 2014 5:35 AM

All replies

  • activecell is the column which includes cheque date. i check this on 15.12.14 so i test it directly with out using current date.15 is refers to 2015 & 12 refers December & 15 refers 15th of December. 
    Tuesday, December 16, 2014 5:39 AM
  • Hi,

    I think you should place your msgbox inside the loop like this:

    --------------------

         Else
         str = str + ActiveCell.Offset(0, -1).Text & "    " & ActiveCell.Text & "    " & ActiveCell.Offset(0, 1).Text & "    " & ActiveCell.Offset(0, 2).Text & "    " & vbCrLf
       
    MsgBox str     

         End If
         ActiveCell.Offset(1, 0).Select
      End If
    End If
    Loop
    ---------------------------

    Reading your question i think you want output from every cycle it makes right? 


    Maurice

    Tuesday, December 16, 2014 7:07 AM
  • Hi,

    I think you should place your msgbox inside the loop like this:

    --------------------

         Else
         str = str + ActiveCell.Offset(0, -1).Text & "    " & ActiveCell.Text & "    " & ActiveCell.Offset(0, 1).Text & "    " & ActiveCell.Offset(0, 2).Text & "    " & vbCrLf
       
    MsgBox str     

         End If
         ActiveCell.Offset(1, 0).Select
      End If
    End If
    Loop
    ---------------------------

    Reading your question i think you want output from every cycle it makes right? 


    Maurice

    thanks for your early reply i thinks your answer should give me each msgbox for each cheque date below 15.12.14 .isn't it ? all i want is single msgbox that displays cheque details below that date one by one line. 
    Wednesday, December 17, 2014 11:01 AM
  • This will give you the output one by one.

    Maurice

    Sunday, December 21, 2014 11:43 AM
  • but i dont want output one by one.i just want a single msgbox displaying cheques didn't banked yet 
    Wednesday, December 31, 2014 9:10 AM
  • Hi K

    The second If statement does not appear to be referring to the correct variable...

    It should read: 

            If txt2 > 12 Then

    This I hope will resolve the problem...

    R

    Friday, January 2, 2015 1:46 PM