issues with Replace / issues with variable type?

[PossiblyNot]

Hi all

I've been having some problems with the running the sub below (still WIP). running the macro kicks up an error, with ReplacedText.Value being highlighted, with the message "constant expression required". I think its an issue from earlier where I try to assign ReplacedText's value, maybe due to issues with range type variables?

any ideas?

(p.s. the macro is handling data listed here. I've given up on cross workbook referencing and have moved the column of reference ages to the same sheet. I hope to search for an age name in the reference data column, select the numerical data a column to the right of it (hence the offset cell) and copy and replace the name in the data table with the numerical age)

Sub AgeSubst()


    'start loops at R2C5
    x = 2
    y = 5


    
    
    
        'loop along the columns
        Do While Cells(2, y).Value <> ""
            
            'nested loop down row
                Do While Cells(x, y).Value <> ""
                
                'selects current cell, sets current cell contents as a String f
                    Set Starting.Address = Range(x, y)
                    Starting.Select
                
                Dim f As String
                f = ActiveCell.Value
                
                
                    'searching for f, resultant answer saved as c, c selected
                    With Worksheets(3).Range("R1C1:R192:C1")
                        Set c = .Find(f, LookIn:="xlvalues")
                        c.Select
                                
                                'declaring intermediate cell addresses as ranges, recording address of Active Cell (c)
                                Dim rngActiveCell As Range
                                Dim rngCellRight As Range
                                
                                Set rngActiveCell = ActiveCell
                                
                                'selecting the cell right of c
                                
                                Set rngCellRight = rngCurrentCell.Offset(0, 1)
                                
                                    'setting parameters for replace function:
                                    'OriginalText.Value - What to replace
                                    'ReplacedText.Value - What to replace with


                                    
                                    Set ReplacedText.Value = rngCellRight.Value
                                    
                   
                                    
                                        'Replacing
                                        
                                      Dim Replace(OriginalText.Value, OriginalText.Value, ReplacedText.Value) As String
                                      
                                    
           
            x = x + 1
            Loop
        y = y + 1
        Loop
        
End Sub

 

[hippiehacker]

Sub possiblynot()
Dim rFound As Range
For Each cell In Sheets("Sheet1").Range("B2:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row)
    On Error Resume Next
    With Sheets("Sheet2")
        Set rFound = .Columns(1).Find(What:=cell, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    On Error GoTo 0
        If Not rFound Is Nothing Then
         cell.Value = rFound.Offset(0, 1).Value
        End If
    End With
 Next cell
End Sub