Run-time error 9 - subscript out of range


D

darkside7out

I inherited a program from a now defunct company that I want to use. My
problem is that it uses a great deal of VBA code and I am getting errors when
I try to move this code to my renamed file. The first issue is in the
following code at the line:

Load frmfinder



Sub CallFinder()
Dim rngItem As Range
Dim rngSelection As Range
Dim intRow As Integer
Dim strMod As String
On Error Resume Next
Set rngSelection = Selection
If Err.Number <> 0 Then
MsgBox "You must select a cell in the Cabinet/Part Number column."
Exit Sub
End If
On Error GoTo 0
Set rngItem = ActiveSheet.Range("c18:c57")
If Intersect(rngItem, rngSelection) Is Nothing Then
MsgBox "You must select a cell in the Cabinet/Part Number column."
Exit Sub
End If
If rngSelection.Value <> 0 Then
If MsgBox("Do you want to replace Cabinet/Part Number: " &
rngSelection.Value, vbYesNo) = vbNo Then Exit Sub
End If
Load frmFinder
frmFinder.Show
If Len(frmFinder.strItemCode) > 0 Then
blnIgnoreWorksheetChange = True
rngSelection.Value = frmFinder.strItemCode
intRow = rngSelection.Row
rngSelection.Offset(0, 1).Formula = "=IF(ISNA(R" & intRow &
"),0,IF($c" & intRow & "=0,0,VLOOKUP($c" & intRow & ",CATALOGLIST,6,0)*(b" &
intRow & ")))"
rngSelection.Offset(0, 3).Formula = "=IF(ISNA(R" & intRow &
"),0,IF($c" & intRow & "=0,0,VLOOKUP($c" & intRow & ",CATALOGLIST,7,0)*(b" &
intRow & ")))"
'rngSelection.Offset(0, 5).Formula = "=IF(ISNA(R" & intRow &
"),0,IF($E" & intRow & "=0,0,VLOOKUP($E" & intRow & ",CATALOGLIST,4,0)))"
'rngSelection.Offset(0, 5).Formula = "=IF(VLOOKUP($E" & intRow &
",CATALOGLIST,5,0)=""NA"",""Not available, choose another item"",IF(ISNA(R" &
intRow & "),0,IF($E" & intRow & "=0,0,VLOOKUP($E" & intRow &
",CATALOGLIST,4,0))))" '10/01/07
rngSelection.Offset(0, 5).Formula = "=IF(AND(OR(_Frame=""5/8
Frameless"",_Frame=""3/4 Frameless""),VLOOKUP($c" & intRow &
",CATALOGLIST,5,0)=""NA""),""Not available, choose another item"",IF(ISNA(R"
& intRow & "),0,IF($c" & intRow & "=0,0,VLOOKUP($c" & intRow &
",CATALOGLIST,4,0))))" '10/02/07
'rngSelection.Offset(0, 9).Formula = "=IF(ISNA(R" & intRow &
"),0,IF(R" & intRow & "<1,R" & intRow & "*T" & intRow - 1 & "*D" & intRow &
",R" & intRow & "*D" & intRow & "))"
'rngSelection.Offset(0, 9).Formula = "=IF(VLOOKUP($E" & intRow &
",CATALOGLIST,5,0)=""NA"",NA(),IF(ISNA(R" & intRow & "),0,IF(R" & intRow &
"<1,R" & intRow & "*T" & intRow - 1 & "*D" & intRow & ",R" & intRow & "*D" &
intRow & ")))" '10/1/07
rngSelection.Offset(0, 9).Formula = "=IF(AND(OR(_Frame=""5/8
Frameless"",_Frame=""3/4 Frameless""),VLOOKUP($c" & intRow &
",CATALOGLIST,5,0)=""NA""),NA(),IF(ISNA(R" & intRow & "),0,IF(R" & intRow &
"<1,R" & intRow & "*j" & intRow - 1 & "*b" & intRow & ",R" & intRow & "*b" &
intRow & ")))" '10/2/07
rngSelection.Offset(0, 10).Formula = "=IF(ISNA(R" & intRow &
"),0,IF(S" & intRow & "<1,S" & intRow & "*j" & intRow - 1 & "*b" & intRow &
",(S" & intRow & "*b" & intRow & ")))"

If UCase(Trim(frmFinder.strPromptForMod)) = "TRUE" Then
strMod = InputBox("Enter Modification Size / Location: ")
rngSelection.Offset(0, 6).Value = strMod
End If
blnIgnoreWorksheetChange = False
End If
Unload frmFinder
End Sub
 
Ad

Advertisements

P

Per Jessen

Hi

frmFinder is a Userform, which you assumingly don't have transfered to your
own workbook.

Open the VBA editor with the original workbook open, and look in the project
explorer in the left side of the screen. Rightclick frmFinder, choose export
file, then goto your own workbook, and import the file.

Hopes this helps.
....
Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top