Help with variables

NewUser598

New Member
Joined
Dec 4, 2009
Messages
13
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?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
On Error Resume Next only needs to be at the top.

Also use the code tags when posting code like so:

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, 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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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

New Member
Joined
Dec 4, 2009
Messages
13

ADVERTISEMENT

Thanks for all the help
 

NewUser598

New Member
Joined
Dec 4, 2009
Messages
13
Im getting an "Object required" Error on the statement:

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

In the code:

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.
 
Last edited:

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
You have:

FoundCell.Select

But earlier you said FoundCell = First

First = the value A3

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

Watch MrExcel Video

Forum statistics

Threads
1,127,215
Messages
5,623,434
Members
415,974
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top