Problem with formula [Closed]

Report
Posts
2
Registration date
Monday June 12, 2017
Status
Member
Last seen
June 13, 2017
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
I have a record in cell A, I want to find it in a range of records in cell b, if found then I want to look at column c for this record, if column c is blank or zero then returns an "x" if column c is not blank then leave column c blank... I had tried and I cannot seem to get it to work

3 replies

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi tai8,

Your query doesn't make much sense to me.

You want to compare column A to column B, when record found check column C. When column C is empty or zero, then the record is not found, you want to return an 'x'. When column C is not empty (does it matter if it found the record?), you want to empty the cell.

So you want to loop through column C and replace all empty and zero cells by 'x' and clear all the cells that have a record which is found in both column A and B.

Is this what you are after? Am I even close?

Best regards,
Trowa
Posts
2
Registration date
Monday June 12, 2017
Status
Member
Last seen
June 13, 2017

You are almost there,

You want to compare column a record in column A to a range of records in column B, when record is found in column then check column C for that record. If column column C is empty or zero, then return an "X' . if column c is not empty or zero then leave it blank (does it matter if it found the record?), Yes, it does matter if the record is found. I want the solution to be posted in column E. I am making sense?
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Let's see if we can get there all the way :)

Give the following code a try in a copy of your workbook:
Sub RunMe()
Dim mFind As Range

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set mFind = Columns("B").Find(cell.Value)
    If Not mFind Is Nothing Then
        Set mFind = Columns("C").Find(cell.Value)
        If Not mFind Is Nothing Then mFind.ClearContents
    End If
Next cell

For Each cell In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value = vbNullString Or cell.Value = 0 Then cell.Offset(0, 2).Value = "x"
Next cell
End Sub


Since you asked for a formula and instead get a macro code, here is how to use it:
How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.

Best regards,
Trowa