PDA

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



koala
10-13-2010, 06:36 AM
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 cellsI 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
10-13-2010, 09:56 AM
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
10-14-2010, 12:20 AM
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 :thumb

GTO
10-14-2010, 07:00 AM
You are very welcome and I am glad the commenting helps a bit :beerchug: