Search - Find - Replace Macro UDM

[Excellerator]

Howdy -

I have a small problem with my if statement

I was wondering if anyone would be able to see the error in the code by looking at it


Here is my code:

Sub Find()

Dim k As Variant
Dim r As Variant
Dim c As Variant
Dim ref As Object
Dim refplayer As Range
Dim StartCell As String
Dim id As String

Do

Sheets("Players.ehm").Select

Range("J3").Activate


For r = 3 To Range("J28103").End(xlUp).Row Step 20

If Sheets("Players.ehm").Cells(r, "J").value > "30" Then

Cells(r, "A").Activate

id = ActiveCell.Text

Sheets("Sheet1").Select

Range("A1").Activate

Cells.Find(What:=id, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

StartCell = ActiveCell.Address

ActiveCell.Offset(1, 3).Activate

ActiveCell.Formula = "=OFFSET(Stats.ehm!$C$8,(ROW($G$1)+B3)*25,0)"

Sheets("Players.ehm").Select

End If

Next r

Loop Until IsEmpty(StartCell)

End Sub


Also the Loop Until Is Empty might not be working - I think because in the macro i'm switching worksheets back and forth.

The macro works but when it replaces some values in Sheet1 are not greater then 30. (i.e. my if statement)

Without writing a thesis - If u have any questions - I can answer them as this workbook is fairly structured

any help will be apprecaited

thanks

Nick

[mudraker]

Nick

Try this modification

It does away with the need to keep activating different sheets and cells
I think it also overcomes your loop problem


Sub Find()

Dim k As Variant
Dim r As Variant
Dim c As Variant
Dim ref As Object
Dim refplayer As Range
Dim StartCell As String
Dim id As String
Dim wsEHM As Worksheet

Set wsEHM = Sheets("Players.ehm")
Sheets("Sheet1").Select
Do

For r = 3 To wsEHM.Range("J28103").End(xlUp).Row Step 20

If wsEHM.Cells(r, "J").Value > "30" Then

id$ = Cells(r, "A").Text

Range("A1").Activate
On Error GoTo ExitLoop
StartCell$ = Cells.Find(What:=id, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
On Error Resume Next
Range(StartCell).Offset(1, 3).Formula = "=OFFSET(Stats.ehm!$C$8,(ROW($G$1)+B3)*25,0)"

End If

Next r

Loop Until StartCell = ""
ExitLoop:
End Sub