1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro that overwrites info in 1st column based on info in a 2nd column

Discussion in 'Business Applications' started by mamcelr, Apr 26, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    Excel 2007 for PC

    Rookie Excel user requesting help writing a macro that will overwrite information in one column, based on information in a second column. See attached file.
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    hi
    See if this works for you. I made a couple of assumptions

    1. Your actual worksheet will start at row 1 with headers at row 2 onwards with data
    2. The data is in the 1st tab
    I've set up the attached to reflect the assumptions

    here is the code

    Code:
    Sub RepColNum()
    'Macro that does the following: IF C8 = 0 or is blank, then B8 remains unchanged,
    'IF C8 > 0 or is not blank, then B8 is overwritten to equal C8.
    '
    Sheets(1).Activate
    '
    lRow = Cells(Rows.Count, 2).End(xlUp).Row
    '
    For i = 2 To lRow
        If Cells(i, "C") <> "" And Cells(i, "C") <> 0 Then
            Cells(i, "B") = Cells(i, "C")
        End If
    Next
    '
    End Sub
    
    the file is attached
     

    Attached Files:

  3. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    XCubed,



    Thank you. I tried this as soon as I got to work this morning and it worked great in the test file. Now I need to convert it to the actual working file. In that file the following is true:
    1. Worksheet Name is "Loan Data"
    2. Worksheet starts at "A1"
    3. Worksheet headers are in "Row 1"
    4. Data begins at "Row 2"
    5. Column to be overwritten is "M"
    6. Column with data is "Y"
    With that in mind I tried the follwoing edits and it did't quite work. I think the problem is in the begining, where the macro determines what row to start in . . . your thoughts?



    Sub Macro2()
    'Macro that does the following: IF Y2 = 0 or is blank, then M2 remains unchanged,
    'IF Y2 > 0 or is not blank, then M2 is overwritten to equal Y2.
    '
    Sheets(Loan Data).Activate
    '
    lRow = Cells(Rows.Count, 2).End(xlUp).Row
    '
    For i = 2 To lRow
    If Cells(i, "Y") <> "" And Cells(i, "Y") <> 0 Then
    Cells(i, "M") = Cells(i, "Y")
    End If
    Next
    '
    End Sub
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi manclr

    you almost had it - there were only 2 changes needed.

    When referring to the worksheet you needed quotes around the name and
    In most cases I wouldn't necessarily even use this line but because you are changing data irreversibly I wanted to make sure it happens where it's supposed to.

    Second, when searching for the last row to evaluate it was referring to "2" which is column B. It should probably be 13 or Column M

    here is the updated macro

    Code:
    Sub Macro2()
    'Macro that does the following: IF Y2 = 0 or is blank, then M2 remains unchanged,
    'IF Y2 > 0 or is not blank, then M2 is overwritten to equal Y2.
    '
    Sheets("Loan Data").Activate
    '
    lRow = Cells(Rows.Count, "M").End(xlUp).Row
    '
         For i = 2 To lRow
            If Cells(i, "Y") <> "" And Cells(i, "Y") <> 0 Then
                Cells(i, "M") = Cells(i, "Y")
            End If
         Next
    '
    End Sub
     
  5. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    Outstanding, outstanding, oustanding . . . !!

    I have over 11,000 lines of info this had to tested for and it's getting done is less than a second! Thank you . . . you've been a huge help!!
     
  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Is it that slow! ;)

    You're very welcome
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1097179

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice