Macro to change a cell based on another cell in the same row

Posted on 2009-12-16
Last Modified: 2012-05-08
Hi  Experts

I am trying to write a macro that will go through a worksheet (about 20,000 rows of data) and check every cell in column C and if the values are either "Cash" or "Paid" then change a cell in the same row but in Column N to become "Checked" and "Added" respectively.  Does this make any sense?  I have been trying to record some Macros but nothing seems to work.  Note I want to solve this through a Macro not a formula appoach.  

Thanks for looking at my question.

Question by:matt_m
    LVL 23

    Accepted Solution

    Here is code and a working workbook.  Just download the workbook and run the macro..


    Sub SetValues()
    Dim r As Range
    Dim MaxRow As Long
    MaxRow = Range("C" & Cells.Rows.Count).End(xlUp).Row
    Set r = Range("C1") ' first cell with data to check
        Do Until r.Row > MaxRow
             Select Case LCase(r.Value)
                Case "cash"
                    Cells(r.Row, "N").Value = "Checked"
                    Set r = r.Offset(1, 0)
                Case "paid"
                    Cells(r.Row, "N").Value = "Added"
                    Set r = r.Offset(1, 0)
                Case Else
                    Set r = r.Offset(1, 0)
            End Select
    End Sub

    Open in new window


    Author Closing Comment

    Thank you - works great!
    LVL 23

    Expert Comment

    Thanks for the points and the grade!  I'm glad it worked for you.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now