Solved: Subscript out of range

[YellowLabPro]

I am testing on this block of code, it is in the rough draft stages. I breaks at this line- Worksheets("EC Products").Range("J4:J" & LRowc).Replace "39208", "5-6", xlWhole, False
which is stumping me. The workbook is open and I have checked the spelling of the sheet name. All looks ok...


Sub DougsFindAndReplacev2()
Dim Wsc As Worksheet
Dim c As Range, rng As Range
Dim LRowc As Long
Dim shCalcSetting As Long
Set Wsc = Workbooks("Complete_Upload_File.xls").Worksheets("EC Products")
LRowc = lr(Wsc, 1)
Set rng = Wsc.Range("J4:J" & LRowc)
shCalcSetting = Application.Calculation
'Application.ScreenUpdating = false
Application.Calculation = xlManual


'With Wsc
'For Each c In rng
'c.Value.Replace "39208", "5-6", xlWhole, False
'End With
'Next c

Worksheets("EC Products").Range("J4:J" & LRowc).Replace "39208", "5-6", xlWhole, False

' Range("M4:M" & LRowf).Replace "L", "Lg", xlWhole, False
' Range("M4:M" & LRowf).Replace "LARGE", "Lg", xlWhole, False
' Range("M4:M" & LRowf).Replace "LG", "Lg", xlWhole, False
' Range("M4:M" & LRowf).Replace "LRG", "Lg", xlWhole, False
' Range("M4:M" & LRowf).Replace "L/Xl", "Lg/Xl", xlWhole, False
'End With
ActiveSheet.Calculate
Application.Calculation = shCalcSetting
Application.ScreenUpdating = True
End Sub

[daniel_d_n_r]

"J4:J".........??

EDIT: oops sorry that bit works ok

[YellowLabPro]

That is finding the last used cell in the row

[YellowLabPro]

And it is accurate, it finds the last row is 121453

[rory]

Are you using XL2007 now? You can't have row 121,453 otherwise!

[daniel_d_n_r]

Worksheets("Sheet1").Columns("A").Replace What:="SIN", Replacement:="COS", SearchOrder:=xlByColumns, MatchCase:=True I am on office 2000, so i guess i should think about that before i make attempts at helping people.This is the only example i found where it seems to be decared differenty.but it could be perfectly ok depending on your version. cheers

[YellowLabPro]

Haha!
No that was a typo-
12,453

[rory]

Yup - it should be:
.Replace "39208", "5-6", xlWhole, , False
or use named arguments.

[YellowLabPro]

Daniel,
No worries mate. I am on 2003 so that was fine. But that is not the issue I don't believe.
From my experience- the error is when it cannot find the workbook or worksheet.

[rory]

No, the issue is that you are supplying False as the SearchOrder parameter. The acceptable values for the XlSearchOrder enum are 1 or 2 and False ( zero) is not valid.

[YellowLabPro]

Rory-
Brilliant- thanks.

Note* Daniel and Rory- the experience I have had w/ RT Error 9 Out of range as I stated was the book or sheet was not open. I had yet to experience it when a argument was missing, or in this case out of order.

BTW: Is there are error reference list that will show what type errors occur w/ which error messages?

[rory]

I'm not aware of any particular references for errors. The descriptions are usually slightly helpful apart from the generic Application errors.
This one is out of range because the item you are trying to refer to (in this case in an Enum) does not exist, so it's the same basic cause as the worksheet name being incorrect.