date from net compare to today issuing type mis-match

[vlady]

any though here ...type mismatch on date.

For Each rep In Rng1.Cells   '-> this is from...   Set Rng1 = .[a1:a1500]
         strMyChar = rep.Value
         replace = rep.Offset(0, 2).Value   '-> offset range to date location (range)
        strMyReplace = rep.Offset(0, 1).Value
               With Rng2
                     If replace = Mydate Then    'type mismatch here
                           .replace What:=strMyChar, Replacement:=strMyReplace, _
                            SearchOrder:=xlByColumns, MatchCase:=True
                     End If
        End With
Next rep

[AlphaFrog]

It looks like you are comparing a string to a date. A date is a number.

This would produce the same error...

If "Text" = 1 Then

How is MyDate declared and set?
What exactly is in rep.Offset(0, 2).Value? A Serial date or a Text-date?
How is replace declared?

[vlady]

you mean the offset is returning a string instead of the number(date).
i checked the dates they are numbers(i tried to add ) also formatted as *3/14/2001

[vlady]

They are Serial dates

 Dim ws As Worksheet, wb As Workbook
    Dim fList As Variant, i As Integer
    Dim Rng1, Rng2, rep As Range
    Dim Mydate As Date
    Mydate = Date
   Dim strMyChar As String, strMyReplace As String
   Dim replace As String
           With ActiveWorkbook.Worksheets("Sheet4")
                    Set Rng1 = .[a1:a1500]  ' i set this up to 1500 rows incase there will be more updates
        End With
    With ActiveWorkbook.Worksheets("Data")
        Set Rng2 = .[A1:Z500]   ' this is the range where it will change all instances
    End With
     
    
       For Each rep In Rng1.Cells
       strMyChar = rep.Value
       replace = rep.Offset(0, 2).Value
        strMyReplace = rep.Offset(0, 1).Value
          With Rng2
           If replace = Mydate Then
            .replace What:=strMyChar, Replacement:=strMyReplace, _
            SearchOrder:=xlByColumns, MatchCase:=True
             End If
        End With
          Next rep

[AlphaFrog]

Dim Mydate As Date
Dim replace As String

MyDate is a Date.
replace is a string and not a date.

[vlady]

lol, Why did not I see that I was going round and round...
Thank you very much.

[AlphaFrog]

You're welcome.

Also note;

Dim Rng1, Rng2, rep As Range

In this declaration, only rep is type Range. Rng1 and Rng2 are both type variant unless you explicitly declare the type for each e.g.;

Dim Rng1 As Range, Rng2 As Range, rep As Range

[vlady]

yup. thanks again.