More complex find and replace

[daseffects]

I have the code at the bottom which currently copies over only rows w/ values in event1 and pastes them in sheets.relativeevents.

I'm looking to modify it so that in the values it inserts it replaces and 0 values in the rows that it copies w/ 1. Need to avoid inserting 1s in rows that are not copied.

SKIP helped me w/

For Each C In Selection
If C.Value = 0 Then
If Not IsEmpty(C.Value) Then C.Value = 1
End If

when I was trying to scope the problem out. However I've tried a few ways to incorporate it w/ out any success.



Private Sub UpdateRelative()
Dim i As Integer
Dim myrange As Range
Application.Calculation = xlCalculationManual
Sheets("relativeevents&quot.Cells(Sheets("Sumofabsoluteevents&quot.[A1] * 70, 1).Resize(70, 26).ClearContents
'set anchor cell as sheet1
Set myrange = Sheets("event1&quot.Cells(5, 82).CurrentRegion
For Each celrow In myrange.Rows
' if there are any numbers in the row copy the whole row
If Application.Max(celrow) > 0 Or Application.Min(celrow) < 0 Then
i = i + 1
Sheets(&quot;relativeevents&quot.Cells(Sheets(&quot;Sumofabsoluteevents&quot.[A1] * 70 + i, 1).Resize(1, myrange.Columns.Count).Value = celrow.Value
End If
Next celrow
Application.Calculation = xlCalculationAutomatic
End Sub