none
Help running a report (new to Excel Developer)

    Frage

  • Hello!

    Typically I am posting in the Sharepoint forum but my question is having to do with an excel sheet that I've exported from Sharepoint.  I'm hoping someone here can help me figure out how to do something!

    I have a workbook that has a list of Names, Type of Shift they're working, and the amount of hours they worked per shift type.  I get all of this from a sharepoint list that users use to enter their time.

    I have a seperate excel workbook that has a list of the same Names and their payrate for each shift type.

    I need a 3rd workbook that takes the hours worked from the first workbook and finds the right payrate, then gives me a total pay amount for that person.

    Is there a way I can do this programatically?  I have never created a macro but it seems this should be solved simply.  I need to hit a button and have it 1.  Check workbook 1 for the name, shift type and hours then find that same name and shift type in workbook 2 and multiply the two numbers together and spit it out into workbook 3.

    Confusing?  Possible?


    michelle korb

    Mittwoch, 29. August 2012 19:23

Antworten

  • Hi Michelle,

    The "Run-time error '13': Type Mismatch" error is caused by the column has the different types of values to be used. I think you may add the column headings like "Name","Type","Hour" etc to the first row in worksheet, consequently if you start the loop from the first row (set i = 1), the values in the column contains text type but not all the integer type. Therefore, you should start the loop from the second row. For better clarify my idea, I would provide a sample for you.

    The macro is

    Sub Report()
      Dim hour As Integer
      Dim payrate As Double
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim ws3 As Worksheet
      Dim wkb1 As Workbook
      Dim wkb2 As Workbook
      Dim wkb3 As Workbook
      Set wkb1 = Application.Workbooks.Open("D:\Project\test1.xlsx")
      Set wkb2 = Application.Workbooks.Open("D:\Project\test2.xlsx")
      Set wkb3 = Application.Workbooks.Open("D:\Project\test3.xlsx")
      Set ws1 = wkb1.Sheets(1)
      Set ws2 = wkb2.Sheets(1)
      Set ws3 = wkb3.Sheets(1)
      
     'Check and export the data
      For i = 2 To ws1.UsedRange.Rows.Count
       If ws1.Cells(i, 1).Value = ws2.Cells(i, 1).Value And ws1.Cells(i, 2).Value = ws2.Cells(i, 2).Value Then
       hour = ws1.Cells(i, 3).Value
       payrate = ws2.Cells(i, 3).Value
       ws3.Cells(i, 3).Value = hour * payrate
       End If
      Next i
      wkb3.Save
     End Sub

    My test1 and test 2 workbook appears as 

    The result test3 workbook appears as

    Hope this can help you.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    • Als Antwort vorgeschlagen Milan_x Donnerstag, 6. September 2012 06:03
    • Als Antwort markiert Leo_GaoModerator Montag, 10. September 2012 01:28
    Dienstag, 4. September 2012 06:15
    Moderator

Alle Antworten

  • Hi Michelle,

    Thanks for posting in the MSDN Forum.

    Based on your issue, I would suggest you create the three workbook  respectively. The workbook1 includes "Name", "Type" and "Hour" 3 columns; The workbook2 includes "Name", "Type" and "PayRate" 3 columns; The workbook3 includes "Name", "Type" and "PayAmount" 3 columns. Then, you can refer to the following macro for your destination.

    Sub Report()
     Dim hour As Integer
     Dim payrate As Integer
     Dim wkb1 As Workbook
     Dim wkb2 As Workbook
     Dim wkb3 As Workbook
     wkb1 = Application.Workbooks.Open("C:/Workbook1.xlsx")
     wkb2 = Application.Workbooks.Open("C:/Workbook2.xlsx")
     wkb3 = Application.Workbooks.Open("C:/Workbook3.xlsx")
    'Check and export the data 
     For i = 1 To wkb1.Sheets(1).Rows.Count
      If wkb1.Sheets(1).Cells(i, "Name") = wkb2.Sheets(1).Cell(i, "Name") & wkb1.Sheets(1).Cells(i, "Type") = wkb2.Sheets(1).Cell(i, "Type") Then
      hour = wkb1.Sheets(1).Cells(i, "Hour").Value
      payrate = wkb2.Sheets(1).Cell(i, "PayRate").Value
      wkb3.Sheets(1).Cell(i, "PayAmount").Value = hour * payrate
     Next i
    End Sub
    

    Hope this can help you.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Freitag, 31. August 2012 03:01
    Moderator
  • Oh my!  I love this forum!  This will be the first macro I've done.....do I just put this code in the Source for workbook 3?

    Also, I wonder if I'll have any trouble since workbook 1 data is coming from a sharepoint list with a data connection.  I will try it and let you know how it goes.  This will save our payroll person so much time!

    Thanks again 
    Michelle


    michelle korb

    Freitag, 31. August 2012 13:01
  • Hello,

    In testing this I get an error when compiling at this line

     For i = 1 To wkb1.Sheets(1).Rows.Count

    i is hilighted.

    Do I need to do something different.

    Here is the modified code I used for the Macro

    Sub Report()
     Dim hour As Integer
     Dim payrate As Integer
     Dim wkb1 As Workbook
     Dim wkb2 As Workbook
     Dim wkb3 As Workbook
     wkb1 = Application.Workbooks.Open("M:/ExcelTest\Workbook1.xlsx")
     wkb2 = Application.Workbooks.Open("M:/ExcelTest\Workbook2.xlsx")
     wkb3 = Application.Workbooks.Open("M:/ExcelTest\Workbook3.xlsx")
    'Check and export the data
     For i = 1 To wkb1.Sheets(1).Rows.Count
      If wkb1.Sheets(1).Cells(i, "Name") = wkb2.Sheets(1).Cell(i, "Name") & wkb1.Sheets(1).Cells(i, "Type") = wkb2.Sheets(1).Cell(i, "Type") Then
      hour = wkb1.Sheets(1).Cells(i, "Hour").Value
      payrate = wkb2.Sheets(1).Cell(i, "PayRate").Value
      wkb3.Sheets(1).Cell(i, "PayAmount").Value = hour * payrate
     Next i
    End Sub


    Chris Ward

    Freitag, 31. August 2012 21:33
  • Chris,

    Mine didn't work either!  If you add an End if right before "Next i" does that help?  I had to do that but I get an error at the "wkb1 = Application.Workbooks.Open("C:/Users\Michelle\Documents\Book1.xlsx")" line.  :(


    michelle korb

    Sonntag, 2. September 2012 23:22
  • Hi Michelle,

    Sorry for I missed the "End if" in the Macro. In addition, could you please clarify what error have you got? If the error is Run-time error "91": Object variable or With block variable not set , please add "Set" like this

    Set wkb1 = Application.Workbooks.Open("C:\Users\Michelle\Documents\Book1.xlsx")

    Also, if the error is Run-time error "1004": Application-defined or object-defined error, please chech out if the file path is correct and if the file exists in your computer.

    If there is any progress, please feel free to let me know.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us



    Dienstag, 4. September 2012 02:42
    Moderator
  • Hi Leo!

    Sorry, yes I did have the correct format in my macro but typed it wrong here in the thread.  I added the "set" at the beginning of the lines like you added above.  I get the following error:

    "Run-time error '13': Type Mismatch"

    When I click "debug" the line below is highlighted:

    If wkb1.Sheets(1).Cells(i, "Name") = wkb2.Sheets(1).Cell(i, "Name") & wkb1.Sheets(1).Cells(i, "Type") = wkb2.Sheets(1).Cell(i, "Type") Then

    Here is the entire code I am using:

     Sub Report()
     Dim hour As Integer
     Dim payrate As Integer
     Dim wkb1 As Workbook
     Dim wkb2 As Workbook
     Dim wkb3 As Workbook
     Set wkb1 = Application.Workbooks.Open("C:\Users\Michelle\Documents\Book1.xlsx")
     Set wkb2 = Application.Workbooks.Open("C:\Users\Michelle\Documents\Book2.xlsx")
     Set wkb3 = Application.Workbooks.Open("C:\Users\Michelle\Documents\Book3.xlsx")
    'Check and export the data
     For i = 1 To wkb1.Sheets(1).Rows.Count
      If wkb1.Sheets(1).Cells(i, "Name") = wkb2.Sheets(1).Cell(i, "Name") & wkb1.Sheets(1).Cells(i, "Type") = wkb2.Sheets(1).Cell(i, "Type") Then
      hour = wkb1.Sheets(1).Cells(i, "Hour").Value
      payrate = wkb2.Sheets(1).Cell(i, "PayRate").Value
      wkb3.Sheets(1).Cell(i, "PayAmount").Value = hour * payrate
      End If
     Next i
    End Sub

    Thanks for your help!


    michelle korb

    Dienstag, 4. September 2012 02:55
  • Hi Michelle,

    The "Run-time error '13': Type Mismatch" error is caused by the column has the different types of values to be used. I think you may add the column headings like "Name","Type","Hour" etc to the first row in worksheet, consequently if you start the loop from the first row (set i = 1), the values in the column contains text type but not all the integer type. Therefore, you should start the loop from the second row. For better clarify my idea, I would provide a sample for you.

    The macro is

    Sub Report()
      Dim hour As Integer
      Dim payrate As Double
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim ws3 As Worksheet
      Dim wkb1 As Workbook
      Dim wkb2 As Workbook
      Dim wkb3 As Workbook
      Set wkb1 = Application.Workbooks.Open("D:\Project\test1.xlsx")
      Set wkb2 = Application.Workbooks.Open("D:\Project\test2.xlsx")
      Set wkb3 = Application.Workbooks.Open("D:\Project\test3.xlsx")
      Set ws1 = wkb1.Sheets(1)
      Set ws2 = wkb2.Sheets(1)
      Set ws3 = wkb3.Sheets(1)
      
     'Check and export the data
      For i = 2 To ws1.UsedRange.Rows.Count
       If ws1.Cells(i, 1).Value = ws2.Cells(i, 1).Value And ws1.Cells(i, 2).Value = ws2.Cells(i, 2).Value Then
       hour = ws1.Cells(i, 3).Value
       payrate = ws2.Cells(i, 3).Value
       ws3.Cells(i, 3).Value = hour * payrate
       End If
      Next i
      wkb3.Save
     End Sub

    My test1 and test 2 workbook appears as 

    The result test3 workbook appears as

    Hope this can help you.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    • Als Antwort vorgeschlagen Milan_x Donnerstag, 6. September 2012 06:03
    • Als Antwort markiert Leo_GaoModerator Montag, 10. September 2012 01:28
    Dienstag, 4. September 2012 06:15
    Moderator
  • Thank you!  I did finally get it to work as you mentioned.

    The problem is that this assumes that the data in row 1 of book1 matches up with the data in row one of book 2.  I need it to take the first row in book 1 and then cycle through book 2 data until it finds the right payrate, then do the calculation.  Right now its just checking if row 1 = row 1 and if it does it take the payrate.  If not, it doesn't go through to find the right payrate for that person and shift type.

    This may be more complicated than I know how to do obviously!  Thank you for your help so far, perhaps I can figure it out eventually :)


    michelle korb

    Dienstag, 4. September 2012 13:20