Start a cell with a date and after a if be not true keep the same date

[fpa93]

hey guys what i have ..

A column with some task where they are update by the days when they are done. I want to create barcode with those task ... I mean when i read the barcodes it will set the task as done and change the date of the cell for today ( day when was done the task).

what i did was to set a if statement IF(A8=$A$2,TODAY(),""). which compers the whole column with the barcode to find which task it is and so change the cell. however it should not change the others cells if the barcode does not belong to them. Also

Also i wanted to set a previous date before the cell receive the if statement.

 

[Fishboy]

hey guys what i have ..

A column with some task where they are update by the days when they are done. I want to create barcode with those task ... I mean when i read the barcodes it will set the task as done and change the date of the cell for today ( day when was done the task).

what i did was to set a if statement IF(A8=$A$2,TODAY(),""). which compers the whole column with the barcode to find which task it is and so change the cell. however it should not change the others cells if the barcode does not belong to them. Also

Also i wanted to set a previous date before the cell receive the if statement.

Hi fpa93, welcome to the boards.

I am not entirely certain I understand what you are referring to, but I have understood basically:

- You have a value in cell A2 (a barcode number)
- You want to check a range (it seems to be from A8 downwards) to see if any of the values match the value of A2
- If the cell value matches A2 then you want to have the date inserted into (I assume) an adjacent cell
- If the cell value does not match A2 leave the adjacent cell blank
- You want to be able to change the value in A2 to perform additional checks for other barcode numbers

If the above is correct, try out the following macro in a COPY of your workbook:

Sub CompareBarcode()
' Defines variables
Dim Cell As Range, cRange As Range
    ' Sets LastRow as the last row of column A containing data
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        ' Set the check range as A8 to the last row of A
        Set cRange = Range("A8:A" & LastRow)
            ' For each cell in the check range
            For Each Cell In cRange
                ' If the cell value matches the value in A2 then...
                If Cell.Value = Range("A2").Value Then
                    ' Update the adjacent cell in column B with today's date
                    Cell.Offset(0, 1).Value = Date
                End If
            ' Check next cell in check range
            Next Cell
End Sub

 

[fpa93]

Fishboy thank you for your help. it seems it is pretty much what I nee. From now on I will do just some changes in your code to fit it in my worksheet.

As soon as I have any feedback I will let you know.

Thanks for your time.

 

[fpa93]

Hi fpa93, welcome to the boards.

I am not entirely certain I understand what you are referring to, but I have understood basically:

- You have a value in cell A2 (a barcode number)
- You want to check a range (it seems to be from A8 downwards) to see if any of the values match the value of A2
- If the cell value matches A2 then you want to have the date inserted into (I assume) an adjacent cell
- If the cell value does not match A2 leave the adjacent cell blank
- You want to be able to change the value in A2 to perform additional checks for other barcode numbers

If the above is correct, try out the following macro in a COPY of your workbook:

Sub CompareBarcode()
' Defines variables
Dim Cell As Range, cRange As Range
    ' Sets LastRow as the last row of column A containing data
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        ' Set the check range as A8 to the last row of A
        Set cRange = Range("A8:A" & LastRow)
            ' For each cell in the check range
            For Each Cell In cRange
                ' If the cell value matches the value in A2 then...
                If Cell.Value = Range("A2").Value Then
                    ' Update the adjacent cell in column B with today's date
                    Cell.Offset(0, 1).Value = Date
                End If
            ' Check next cell in check range
            Next Cell
End Sub

Fishboy,

It is working but it does not run the macro when the cell (A2) is updated. What should i do ? to it run once and run again if the cell where i read the barcode be updates.

I appreciate your help

 

[Michael M]

change it's name and move to the sheet module as required

Sub worksheet_Change(ByVal target As Range)
'see if range A2 is being changed, if not exit sub
If Intersect(target, Range("A2")) Is Nothing Then Exit Sub
' Defines variables
Dim Cell As Range, cRange As Range
    ' Sets LastRow as the last row of column A containing data
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        ' Set the check range as A8 to the last row of A
        Set cRange = Range("A8:A" & LastRow)
            ' For each cell in the check range
            For Each Cell In cRange
                ' If the cell value matches the value in A2 then...
                If Cell.Value = Range("A2").Value Then
                    ' Update the adjacent cell in column B with today's date
                    Cell.Offset(0, 1).Value = Date
                End If
            ' Check next cell in check range
            Next Cell
End Sub

 

[fpa93]

Dear Michael M,

Thank you a lot for your code. It is working perfectly as it should.

I appreciate your help!!

 

[Fishboy]

Dear Michael M,

Thank you a lot for your code. It is working perfectly as it should.

I appreciate your help!!

Happy to help. Glad to see that Michael could tweak my code to meet your final requirement