Vba: searching value and reporting them in another sheet

[Nelson78]

Hello everybody.

I'm coping with the following situation.

I have to report values from sheet2 to sheet1.

The condition are as follows:

1) not consider data that in sheet2 are in state open (column B);
2) if the state is closed (column B, sheet2), then report in sheet1 on the row corresponding to the code (match column A for both the sheets): the date on the first empty cell, the state "CLOSED" next to the right.

https://imgur.com/a/K5eiKth

So, in this example, sheet1 cell E2 --> 25/06/2018, sheet1 cell F2 --> CLOSED

How can I approach the task?

Thank you.

 

[NoSparks]

Sub TryThis()
    Dim src As Worksheet, dest As Worksheet
    Dim rng As Range, cel As Range, fndRng As Range
    
Set src = ThisWorkbook.Sheets("Sheet2")
Set dest = ThisWorkbook.Sheets("Sheet1")

'the range to work with
Set rng = Range(src.Range("A2"), src.Range("A" & Rows.Count).End(xlUp))
'deal with each cell in that range
For Each cel In rng
    'look for CLOSED in next column
    If cel.Offset(, 1).Value = "CLOSED" Then
        'find the match on sheet1
        With dest
            Set fndRng = .Range("A:A").Find(What:=cel.Value, _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)
            'if match found it will not be nothing
            If Not fndRng Is Nothing Then
                'write date to next empty column
                .Cells(fndRng.Row, .Columns.Count).End(xlToLeft).Offset(, 1).Value = cel.Offset(, 2).Value
            End If
        End With
    End If
Next cel
End Sub

 

[Nelson78]

Sub TryThis()
    Dim src As Worksheet, dest As Worksheet
    Dim rng As Range, cel As Range, fndRng As Range
    
Set src = ThisWorkbook.Sheets("Sheet2")
Set dest = ThisWorkbook.Sheets("Sheet1")

'the range to work with
Set rng = Range(src.Range("A2"), src.Range("A" & Rows.Count).End(xlUp))
'deal with each cell in that range
For Each cel In rng
    'look for CLOSED in next column
    If cel.Offset(, 1).Value = "CLOSED" Then
        'find the match on sheet1
        With dest
            Set fndRng = .Range("A:A").Find(What:=cel.Value, _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)
            'if match found it will not be nothing
            If Not fndRng Is Nothing Then
                'write date to next empty column
                .Cells(fndRng.Row, .Columns.Count).End(xlToLeft).Offset(, 1).Value = cel.Offset(, 2).Value
                .Cells(fndRng.Row, .Columns.Count).End(xlToLeft).Offset(, 1).Value = "CLOSED"
            End If
        End With
    End If
Next cel
End Sub

I have added an instruction to your excellent job.

 

[Nelson78]

                'write date to next empty column
                .Cells(fndRng.Row, .Columns.Count).End(xlToLeft).Offset(, 1).Value = cel.Offset(, 2).Value

Unfortunately, something is wrong.

It doesn't write in the first empty column, but it writes in the first empty column after the last not empty column.

 

[Nelson78]

I have added an instruction to your excellent job.

Maybe

.Cells(fndRng.Row, [COLOR="#FF0000"]"A"[/COLOR]).End(xlTo[COLOR="#FF0000"]Right[/COLOR]).Offset(0, 1).Value = cel.Offset(0, 10).Value

 

[NoSparks]

What happened when you tried that ?