Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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

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.

Cheers
0
matt_m
Asked:
matt_m
  • 2
1 Solution
 
ahammarCommented:
Here is code and a working workbook.  Just download the workbook and run the macro..

:-)
Albert

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
    Loop
                
End Sub

Open in new window

CashOrPaid.xls
0
 
matt_mAuthor Commented:
Thank you - works great!
0
 
ahammarCommented:
Thanks for the points and the grade!  I'm glad it worked for you.

:-)
Albert
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now