Solved: Check two sheets, and amend if criteria met.

[koala]

Hi Forum.

I have two worksheets, Data and Output. Both sheets are identical in size. Workarea is column I:AV, however row length can alter - generally approx 1000 rows. Data has value in every cell, however Output only has values in some cells.

Both sheets have header in I2:AV2, and Date in A3:A

All values are text.

I need to check every cell in I:AV of Data, and if value "XYZ" (capital letters XYZ) are found, check same cell in Output, and if value "m" (small letter m) is found, change "m" to "Y" (captial letter Y).

To summarise: (assume checking cell I5)

• If Data I5 = "XYZ", and Output I5 = "m", change Output I5 to "Y"
• If Data I5 = "XYZ", and Output I5 <> "m", do nothing, keep checking cells
• If Data I5 <> "XYZ", do nothing, keep checking cells

I have attached a sample workbook, with a sheet titled Amended Output which shows the Output I desire. (hightlighted in RED).

Thanks in advance
Koala

[GTO]

Hi koala,

In a Standard Module:

Option Explicit
    
Sub UpdateData()
Dim DataRange As Range, TempRange As Range
Dim FirstAddress As String
    With ThisWorkbook.Worksheets("Data") '//Or use the sheet's codename//
        
        Set TempRange = RangeFound(.Range(.Cells(3, "I"), .Cells(.Rows.Count, "AV")))
        '// Bail if empty sheet//
        If TempRange Is Nothing Then Exit Sub
        
        '// set a reference to the range on Data sheet  //
        Set DataRange = .Range(.Cells(3, "I"), TempRange)
    
    End With
    
    Set TempRange = Nothing
    '// find an initial cell with XYZ...    //
    Set TempRange = RangeFound(DataRange, "XYZ", , , xlWhole, , , True)
    
    With DataRange
        If Not TempRange Is Nothing Then
            '// ...then loop until we run into the same cell again...   //
            FirstAddress = TempRange.Address
            Do
                '// checking against the other sheet for the same address   //
                If ThisWorkbook.Worksheets("Output").Cells(TempRange.Row, TempRange.Column).Value = "m" Then
                    ThisWorkbook.Worksheets("Output").Cells(TempRange.Row, TempRange.Column).Value = "Y"
                    '// just to show results //
                    ThisWorkbook.Worksheets("Output").Cells(TempRange.Row, TempRange.Column).Interior.ColorIndex = 3
                End If
                Set TempRange = .FindNext(TempRange)
            Loop While Not TempRange.Address = FirstAddress
        End If
    End With
End Sub
    
Function RangeFound(SearchRange As Range, _
                    Optional FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function

Hope that helps :-)

Mark

[koala]

Thank you GTO.

That works perfectly. Thank you also for commenting the code. It helps to make it easier for me to understand. (well actually I only understand some small parts, it is way too complicated for me to understand it all).

Once again, many thanks

cheers
Koala

[GTO]

You are very welcome and I am glad the commenting helps a bit