Help with variables

[NewUser598]

I am trying to declare a variable that i can reference a cell value to activate then copy that value to another range of cells. My effort so far has been faulty. This is what Ive done so far:
Sub ReplaceFormat()
Dim First As Integer
First = Range("A3").Value
Set FoundCell = First
Application.ReplaceFormat.Interior.ColorIndex = 3 '(Red)
Cells.Replace What:="First", Replacement:="First", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, _
SearchFormat:=False, ReplaceFormat:=True
If FoundCell = First Then
FoundCell.Select
Selection.Copy Destination:=Cells(MyDataRow, 10)
ActiveCell.Offset(-1, -1).Copy Destination:=Cells(MyDataRow, 11)
On Error Resume Next
ActiveCell.Offset(0, -1).Copy Destination:=Cells(MyDataRow, 12)
On Error Resume Next
ActiveCell.Offset(1, -1).Copy Destination:=Cells(MyDataRow, 13)
On Error Resume Next
ActiveCell.Offset(1, 0).Copy Destination:=Cells(MyDataRow, 14)
On Error Resume Next
ActiveCell.Offset(1, 1).Copy Destination:=Cells(MyDataRow, 15)
On Error Resume Next
ActiveCell.Offset(0, 1).Copy Destination:=Cells(MyDataRow, 16)
On Error Resume Next
ActiveCell.Offset(-1, 1).Copy Destination:=Cells(MyDataRow, 17)
On Error Resume Next
ActiveCell.Offset(-1, 0).Copy Destination:=Cells(MyDataRow, 18)
On Error Resume Next
MyDataRow = MyDataRow + 1
End If
End Sub

Any recomendations as to how to make this work?

 

[Norie]

I think the first thing would be to remove all those 'On Error Resume Next' bits, then perhaps in words explain what you want the code to do.

 

[Blade Hunter]

On Error Resume Next only needs to be at the top.

Also use the code tags when posting code like so:

Sub ReplaceFormat()
On Error Resume Next
Dim First As Integer
First = Range("A3").Value
Set FoundCell = First
Application.ReplaceFormat.Interior.ColorIndex = 3 '(Red)
Cells.Replace What:="First", Replacement:="First", LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=True
If FoundCell = First Then
    FoundCell.Select
    Selection.Copy Destination:=Cells(MyDataRow, 10)
    ActiveCell.Offset(-1, -1).Copy Destination:=Cells(MyDataRow, 11)
    ActiveCell.Offset(0, -1).Copy Destination:=Cells(MyDataRow, 12)
    ActiveCell.Offset(1, -1).Copy Destination:=Cells(MyDataRow, 13)
    ActiveCell.Offset(1, 0).Copy Destination:=Cells(MyDataRow, 14)
    ActiveCell.Offset(1, 1).Copy Destination:=Cells(MyDataRow, 15)
    ActiveCell.Offset(0, 1).Copy Destination:=Cells(MyDataRow, 16)
    ActiveCell.Offset(-1, 1).Copy Destination:=Cells(MyDataRow, 17)
    ActiveCell.Offset(-1, 0).Copy Destination:=Cells(MyDataRow, 18)
    MyDataRow = MyDataRow + 1
End If
End Sub

 

[Gary McMaster]

As Norie pointed out, you don't need all the "On Error Resume Next" statements. When that statement is executed in a procedure, all errors from then on will be ignored until the statement "On Error Goto 0" is encountered or the procedure ends.

Generally you should not use it during developement. It makes debugging much harder. When you do use it, you should only do so where you really expect an error to occur at the next statement and then you should turn it off with "On Error Goto 0" after the expected error point.

Gary

 

[NewUser598]

Thanks for all the help

 

[NewUser598]

Im getting an "Object required" Error on the statement:

Set FoundCell = [COLOR=slategray]First[/COLOR]

In the code:

Sub ReplaceFormat()
On Error Resume Next
Dim First As Integer
First = Range("A3").Value
Set FoundCell = First
Application.ReplaceFormat.Interior.ColorIndex = 3 '(Red)
Cells.Replace What:="First", Replacement:="First", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, _
SearchFormat:=False, ReplaceFormat:=True
    If FoundCell = First Then
    FoundCell.Select
    Selection.Copy Destination:=Cells(MyDataRow, 10)
           ActiveCell.Offset(-1, -1).Copy Destination:=Cells(MyDataRow, 11)
           ActiveCell.Offset(0, -1).Copy Destination:=Cells(MyDataRow, 12)
           ActiveCell.Offset(1, -1).Copy Destination:=Cells(MyDataRow, 13)
           ActiveCell.Offset(1, 0).Copy Destination:=Cells(MyDataRow, 14)
           ActiveCell.Offset(1, 1).Copy Destination:=Cells(MyDataRow, 15)
           ActiveCell.Offset(0, 1).Copy Destination:=Cells(MyDataRow, 16)
           ActiveCell.Offset(-1, 1).Copy Destination:=Cells(MyDataRow, 17)
           ActiveCell.Offset(-1, 0).Copy Destination:=Cells(MyDataRow, 18)
           MyDataRow = MyDataRow + 1
    End If
End Sub

I am trying to set the variable First to represent the value in Cell A3. I want the code to find all the A3 values throughout my 2000+ Integers in my worksheet.

 

[Blade Hunter]

You have:

FoundCell.Select

But earlier you said FoundCell = First

First = the value A3

You can't select a value. Maybe FoundCell = First.Address??