Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help cleaning up macros (Excel 2003)

    Hey guys,

    I have been working on my coding of this project, and I think it is really getting ugly! I am trying to make it so that when a user inputs his info into the calculator, he can hit a button and have it transfer into a spearsheet to track trends. I have had some success, but some of the macros are not advancing to the next open row, which is something that I need to make happen. Could someone take a look at my workbook and offer some advice. I would like to make it as easy as possible, but I am not a pro at the code, and it looks like it should be working from what I can see.
    Attached Files Attached Files
    • File Type: zip 1.zip (44.6 KB, 6 views)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help cleaning up macros (Excel 2003)

    Most of the procedures appear to be working correctly, but in two of them, you're obtaining the last row in the wrong column:

    1) In Transfer150SuspB (in Module2), you're copying data to columns G through J (7 to 10). So you must get the last row in column 7 instead of 2. The line

    lRow = .Cells(.Cells.Rows.Count, 2).End(xlUp).Row + 1

    should be changed to

    lRow = .Cells(.Cells.Rows.Count, 7).End(xlUp).Row + 1

    2) In TransferLexusB (in Module6), you're copying data to columns F through H (6 to 8). So you must get the last row in column 6 instead of 2. The line

    lRow = .Cells(.Cells.Rows.Count, 2).End(xlUp).Row + 1

    should be changed to

    lRow = .Cells(.Cells.Rows.Count, 6).End(xlUp).Row + 1

    Another remark: in the procedures Transfer150BeamA (in Module3) and Transfer150BeamB (in Module4), you're copying data to column F (6) only. So there is no need to use a For ... Next loop. The lines

    For iCol = 6 To 6
    wksRepair.Cells(lRow, 3) = wks150.Cells(37, 6)
    Next

    can be simplified to

    wksRepair.Cells(lRow, 3) = wks150.Cells(37, 6)

  3. #3
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help cleaning up macros (Excel 2003)

    Thanks for your help again. I have one more question. Say I would like to have the module "Transfer150BeamA()" and "Transfer150BeamB()" also transfer row 53, column 6 as well when it is ran. Do I still need to use the ICol?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help cleaning up macros (Excel 2003)

    No, you just need to add one line: in Transfer150BeamA

    wksRepair.Cells(lRow + 1, 3) = wks150.Cells(53, 6)

    and in Transfer150BeamB

    wksRepair.Cells(lRow + 1, 6) = wks150.Cells(53, 6)

    lRow + 1 is used to copy the data below the row you already filled.

  5. #5
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help cleaning up macros (Excel 2003)

    Hans,

    What if I want to copy the data side by side. The first line refers to column c, but the other one I would like to copy has to go to column b. I did what you wrote above, and it goes under each other, I need them to go beside each other.

    EDIT: Nevermind. I figured it out... Wow, that was easy!!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help cleaning up macros (Excel 2003)

    Change the row and column numbers accordingly.

Posting Permissions

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