none
[Excel] Help with VBA codes RRS feed

  • Question

  • I have an excel where i want to print receipts. The first sheet contains a form setup where i first enter details of the receipt, and with an Command button the details get put into sheet 2 under appropriate headings. I seem to have trouble getting the code set right as it seems to copy the first set of details in sheet2, but newer details overwrite earlier data. Here's the code i'm using:

    Private Sub CommandButton1_Click()
    Dim emptyrow As Long
    Sheet2.Activate
    emptyrow = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveSheet.Cells(emptyrow + 1, 1).Value = Range("date").Value
    ActiveSheet.Cells(emptyrow + 1, 2).Value = Range("name").Value
    ActiveSheet.Cells(emptyrow + 1, 3).Value = Range("number").Value
    ActiveSheet.Cells(emptyrow + 1, 4).Value = Range("its").Value
    ActiveSheet.Cells(emptyrow + 1, 5).Value = Range("mode").Value
    ActiveSheet.Cells(emptyrow + 1, 6).Value = Range("number2").Value
    ActiveSheet.Cells(emptyrow + 1, 7).Value = Range("datec").Value
    ActiveSheet.Cells(emptyrow + 1, 8).Value = Range("amount").Value
    ActiveSheet.Cells(emptyrow + 1, 9).Value = Range("commit").Value
    
    End Sub

    The line "Emptyrow" seems to be causing issues but i know not how ??

    Thursday, March 12, 2015 3:49 AM

Answers

  • See link:

    http://dropcanvas.com/l0i4l

    Your UDF should be in a standard module always.

    To me, everything looks good, if I answered your problem, would you mark the post "Answered"

    Thanks.

    Would like some feedback from you.


    Cimjet

    • Marked as answer by huzzug Thursday, March 12, 2015 6:47 PM
    Thursday, March 12, 2015 5:45 PM

All replies

  • HI

    Try this./ You may need to give us a sample of your file showing the problem.

    The problem is not in the syntax and not able to trouble shout the file, it's all guess work.

    Sub Guessing()
    Dim emptyrow As Long
    Sheet2.Activate
    emptyrow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    ActiveSheet.Cells(emptyrow + 1, 1).Value = Range("date").Value
    ActiveSheet.Cells(emptyrow + 1, 2).Value = Range("name").Value
    ActiveSheet.Cells(emptyrow + 1, 3).Value = Range("number").Value
    ActiveSheet.Cells(emptyrow + 1, 4).Value = Range("its").Value
    ActiveSheet.Cells(emptyrow + 1, 5).Value = Range("mode").Value
    ActiveSheet.Cells(emptyrow + 1, 6).Value = Range("number2").Value
    ActiveSheet.Cells(emptyrow + 1, 7).Value = Range("datec").Value
    ActiveSheet.Cells(emptyrow + 1, 8).Value = Range("amount").Value
    ActiveSheet.Cells(emptyrow + 1, 9).Value = Range("commit").Value

    End Sub

    Let me know if that works. The small change will assure that "emptyrow" is taking the last row from Sheet2.


    Cimjet

    Thursday, March 12, 2015 1:44 PM
  • This line of script: [emptyrow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row]

    That is checking in sheet2 the last entry in column A.

    Should we be checking an other column for the last entry?

    This line will look for the longest row in all columns and paste below it.

    emptyrow = Worksheets("Sheet2").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row


    Cimjet


    • Edited by Cimjet Thursday, March 12, 2015 2:17 PM
    Thursday, March 12, 2015 2:07 PM
  • I tried the code, i get an "run-time error 9".

    Here's the file for you

    http://dropcanvas.com/uu9mr

    Also, column A would always be filled, hence it is my benchmark for next line


    • Edited by huzzug Thursday, March 12, 2015 2:43 PM more info
    Thursday, March 12, 2015 2:21 PM
  • See link with your file, called "Copy Over"

    http://dropcanvas.com/n0u45

    Because I'm using XL2003, I can't save it in XLM format.

    Let me know!!


    Cimjet


    • Edited by Cimjet Thursday, March 12, 2015 3:55 PM
    Thursday, March 12, 2015 3:54 PM
  • Any reason why my file would be acting in such a way ??Could it be a bug or is something wrong with my code. Anyways thanks for the response as always 
    Thursday, March 12, 2015 4:01 PM
  • Is it working okay now ?

    Your worksheet is no longer Sheet1 but sheets("Details").

    I'm busy today, I'm in and out so I may post later.


    Cimjet

    Thursday, March 12, 2015 4:06 PM
  • See link:

    http://dropcanvas.com/l0i4l

    Your UDF should be in a standard module always.

    To me, everything looks good, if I answered your problem, would you mark the post "Answered"

    Thanks.

    Would like some feedback from you.


    Cimjet

    • Marked as answer by huzzug Thursday, March 12, 2015 6:47 PM
    Thursday, March 12, 2015 5:45 PM
  • Sorry to revive this, but what do you mean by "Your UDF should be in a standard module always" ?? Is it the code that resides in command button ??
    Saturday, March 14, 2015 5:07 AM
  • UDF "User defined function" or custom function

    You have four (4) of them like these.

    ' Converts a number from 100-999 into text
    Function GetHundreds(ByVal MyNumber)
        Dim Result As String
        If Val(MyNumber) = 0 Then Exit Function
        MyNumber = Right("000" & MyNumber, 3)
        ' Convert the hundreds place.
        If Mid(MyNumber, 1, 1) <> "0" Then
            Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
        End If
        ' Convert the tens and ones place.
        If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & GetTens(Mid(MyNumber, 2))
        Else
            Result = Result & GetDigit(Mid(MyNumber, 3))
        End If
        GetHundreds = Result
    End Function

    They start with the word "Function" instead of Sub()

    I tested them and highlighted them in green on your worksheet.


    Cimjet

    Saturday, March 14, 2015 2:16 PM