1099 USA tax form
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: 1099 USA tax form

  1. #1
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 1099 USA tax form

    Hi

    Iím trying to build a spreadsheet 1099 tax form for USA tax purposes, so I can complete the form for all the vendors via a marco/s.


    On Sheet1 (Customer Record) I have the customer details Tax Numbers to zip code. This will be done manually as they supply W9 forms.


    Zip Code (Post code) is in column G

    On Sheet2 (Input Data) there will be the vendors data, this will be imported from a WordPress plugin. The plugin will produce a CVS file which can open in excel. The plugin data will NOT hold the Vendors TAX Number (National insurance number). This data I will have already input manually into sheet1 .


    I will copy the plugin data into Sheet2 (Input data).

    I need a macro that will Do the following.



    1. Look for the zip code in Sheet1 Column G and
    2. Then find the same in Sheet2 Column G
    3. Copy data from Sheet2 Column G range G:AB and paste it into sheet1 at the right location, then move to next record.

    If I could code in VB, life wouldn't be such a pain in the A$$

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1099 USA tax form

    try this:

    Code:
    Option Explicit
    Sub Sharid()
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("Sheet1")
        Set s2 = Sheets("Sheet2")
        Dim lr As Long, lr2 As Long, i As Long, j As Long
        lr = s1.Range("G" & Rows.Count).End(xlUp).Row
        lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
        For i = 1 To lr
            For j = 1 To lr2
                If s2.Range("G" & j) = s1.Range("G" & i) Then
                    s2.Range("G" & j & ":AB" & j).Copy
                    s1.Range("H" & i).PasteSpecial xlPasteValues
                End If
            Next j
        Next i
    
    
    End Sub
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  3. #3
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1099 USA tax form

    Thanks Alan

    I have this code for Alan, which does most of what i want, I have just made a minor change to it, to insert and delete a column.

    The code matches the data in column G sheet1 to Data in Sheet2 Column G and copy and pastes it into sheet1, this part is fine.
    The problem I'm having is that it also replaces the headings in sheet1.

    I need it so start from row3, so

    It check sheet1 Row3 for the data
    Matches it in sheet2 starting at row3
    and then pastes it into sheet1 starting at row3.

    Like I said this code works fine apart from it changes the headings that are in row2 on sheet1

    Code:
    Private Sub CommandButton1_Click()
    Range("H1").EntireColumn.Insert
    
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("Customer Record")
        Set s2 = Sheets("Input Data")
        Dim lr As Long, lr2 As Long, i As Long, j As Long
        lr = s1.Range("G" & Rows.Count).End(xlUp).Row
        lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
        For i = 1 To lr
            For j = 1 To lr2
                If s2.Range("G" & j) = s1.Range("G" & i) Then
                    s2.Range("G" & j & ":AB" & j).Copy
                    s1.Range("H" & i).PasteSpecial xlPasteValues
                End If
            Next j
        Next i
    Range("H1").EntireColumn.Delete
    
    
    End Sub
    Also it paste data into row1 on sheet1 that can not be matched, this is normally the last date in the row on sheet2 it cannot match.

    Example: if on sheet2 row66 column G it can't find "123456" which is in Sheet1 then it will copy this data in to sheet1 row1, which is blank only has command button.

    Thanks to ALan for helping on this one, code does work,
    If I could code in VB, life wouldn't be such a pain in the A$$

  4. #4
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1099 USA tax form

    Can anyone help on this one, i'm really stuck

    Thanks
    If I could code in VB, life wouldn't be such a pain in the A$$

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1099 USA tax form

    If I am understanding your request correctly

    Code:
    Option Explicit
    
    
    Private Sub CommandButton1_Click()
    Range("H1").EntireColumn.Insert
    
    
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("Customer Record")
        Set s2 = Sheets("Input Data")
        Dim lr As Long, lr2 As Long, i As Long, j As Long
        lr = s1.Range("G" & Rows.Count).End(xlUp).Row
        lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
        For i = 3 To lr
            For j = 3 To lr2
                If s2.Range("G" & j) = s1.Range("G" & i) Then
                    s2.Range("G" & j & ":AB" & j).Copy
                    s1.Range("H" & i).PasteSpecial xlPasteValues
                End If
            Next j
        Next i
    Range("H1").EntireColumn.Delete
    
    
    
    
    End Sub
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,585
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 1099 USA tax form

    From the OP, it sounds like a VLOOKUP formula is what is needed.

  7. #7
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1099 USA tax form

    Mike
    That was my first thought, but the OP is looking to populate columns G:AB and that seemed to me like a lot of Vlookups. Are you seeing something different?
    Alan
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  8. #8
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1099 USA tax form

    Here is a link for the download file https://app.box.com/s/zjo7063j5485j3mmiq1op4tags54chx8

    Button: Match Data, Alan'c Code and a bit from me.

    1) My bit highlights rows in YELLOW where cells in column P are empty
    2) Deletes imported Column H as it created 2 data sets

    Alan's code matches data from Customer Record Column G with Input data Column G and imports its into Customer Record Sheet. Any records that are not matched, the rows are left blank.

    1)Here is the problem, the last row that is not matched appears in row1 of Customer record, this is shown in green and red for your reff.

    2) Headings will be overwritten in first sheet as it copies headings from second sheet. Currently this is not showing as both sheets have the same headings.

    Button: Clear color, clears the color Yellow

    Button: Clear Data, Clears data from H:AB

    Hope this helps
    If I could code in VB, life wouldn't be such a pain in the A$$

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,585
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 1099 USA tax form

    Quote Originally Posted by alansidman View Post
    Mike
    That was my first thought, but the OP is looking to populate columns G:AB and that seemed to me like a lot of Vlookups. Are you seeing something different?
    Alan
    I was thinking that a bulk "write VLOOKUP formula" and "value=value" would be quicker than a loop of .Finds.

  10. #10
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1099 USA tax form

    ††
    Thanks Alan

    You fixed this yesterday, I did not realise that you had made a small change to your original code, hence my post with the download link.

    Thanks again
    If I could code in VB, life wouldn't be such a pain in the A$$

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com