run time error 9 plz help :(

[hatemhatem]

hi, i have a problem in using vba and each time I run my project I get

Runtime error "9" Subscript out of range :'(

and the line of code in question is:

pr_pri.Worksheets("wspr_pri").Cells(13, 4).Activate

here's the entire code for the macro:

Sub prpri()
Dim i%, j%, k%
Dim iLRA As String, iLRN As String
Dim Y As Boolean, Ys As Boolean
Dim TabloA(), TabloN()
Dim pr As Workbook, pri As Workbook, pr_pri As Workbook
Dim wspr As Worksheet, wspri As Worksheet, wspr_pri As Worksheet
'Détermination du nombre de ligne de Classeur "pr" et "pri" et "pr_pri"
Set pr = Workbooks("PR_DETAIL(GD330_ANLDSV).xls")
Set pri = Workbooks("GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls")
Set pr_pri = Workbooks("PR-PRI Check 2.2 GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls")
Set wspr = pr.Worksheets("PR_DETAIL(GD330_ANLDSV)")
Set wspri = pri.Worksheets("PRI DATA")
Set wspr_pri = pr_pri.Worksheets("Feuil1")
Workbooks("PR_DETAIL(GD330_ANLDSV).xls").Activate
Workbooks("GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls").Activate
Workbooks("PR-PRI Check 2.2 GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls").Activate
pr_pri.Worksheets("wspr_pri").Cells(13, 4).Activate
pr.Worksheets("wspr").Range("D5").Activate
pri.Worksheets("wspri").Range("F9").Activate
iLRA = Worksheets("wspr").Range("D5").Value
Worksheets("wspr_pri").Range("D13").Value = iLRA
iLRN = Worksheets("wspri").Range("F9").Value
Worksheets("wspr_pri").Range("E13").Value = iLRN
If iLRA <> iLRN Then
Worksheets("wspr_pri").Range("H13").Value = "NG"
Else: Worksheets("wspr_pri").Range("H13").Value = "OK"
End If
End Sub

plz help

 

[VoG]

I'm not sure why you are doing all that activating but try

Application.Goto pr_pri.Worksheets("wspr_pri").Cells(13, 4)

 

[Domski]

Have you opened the workbooks before running the code?

Dom

 

[hatemhatem]

yes i have open those file before i run the project, and my first prob was in: iLRA = Worksheets("wspr").Range("D5").Value
Worksheets("wspr_pri").Range("D13").Value = iLRA
iLRN = Worksheets("wspri").Range("F9").Value
Worksheets("wspr_pri").Range("E13").Value = iLRN


but why it does not understand range.value and i had got this error????

 

[hatemhatem]

if i try: Application.Goto pr_pri.Worksheets("wspr_pri").Cells(13, 4)
i had the same prob

 

[hatemhatem]

i changed my code:

Sub prpri()
Dim i%, j%, k%
Dim iLRA As String, iLRN As String
Dim Y As Boolean, Ys As Boolean
Dim TabloA(), TabloN()
Dim pr As Workbook, pri As Workbook, pr_pri As Workbook
Dim wspr As Worksheet, wspri As Worksheet, wspr_pri As Worksheet
'Détermination du nombre de ligne de Classeur "pr" et "pri" et "pr_pri"
Set pr = Workbooks("PR_DETAIL(GD330_ANLDSV).xls")
Set pri = Workbooks("GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls")
Set pr_pri = Workbooks("PR-PRI Check 2.2 GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls")
Set wspr = pr.Worksheets("PR_DETAIL(GD330_ANLDSV)")
Set wspri = pri.Worksheets("PRI DATA")
Set wspr_pri = pr_pri.Worksheets("Feuil1")
Workbooks("PR_DETAIL(GD330_ANLDSV).xls").Activate
Workbooks("GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls").Activate
Workbooks("PR-PRI Check 2.2 GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls").Activate
iLRA = Worksheets("wspr").Range("D5").Value
Worksheets("wspr_pri").Range("D13").Value = iLRA
iLRN = Worksheets("wspri").Range("F9").Value
Worksheets("wspr_pri").Range("E13").Value = iLRN
If iLRA <> iLRN Then
Worksheets("wspr_pri").Range("H13").Value = "NG"
Else: Worksheets("wspr_pri").Range("H13").Value = "OK"
End If
End Sub



and the error is in the ligne: iLRA = Worksheets("wspr").Range("D5").Value
the same error also

 

[VoG]

wspr is a variable so try removing the quotes

iLRA = Worksheets(wspr).Range("D5").Value

 

[hatemhatem]

how can i use the Value of the Cells ("D5") of sheet wspr and done this value to cells ("D13") of sheet wspr_pri????????

 

[VoG]

Try

wspr_pri.Range("D13").Value = wspr.Range("D5")

 

[hatemhatem]

Thx Mister VOG, i have changed my code like this:

Sub prpri()
Dim i%, j%, k%
Dim iLRA As String, iLRN As String
Dim Y As Boolean, Ys As Boolean
Dim TabloA(), TabloN()
Dim pr As Workbook, pri As Workbook, pr_pri As Workbook
Dim wspr As Worksheet, wspri As Worksheet, wspr_pri As Worksheet
'Détermination du nombre de ligne de Classeur "pr" et "pri" et "pr_pri"
Set pr = Workbooks("PR_DETAIL(GD330_ANLDSV).xls")
Set pri = Workbooks("GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls")
Set pr_pri = Workbooks("PR-PRI Check 2.2 GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls")
Set wspr = pr.Worksheets("PR_DETAIL(GD330_ANLDSV)")
Set wspri = pri.Worksheets("PRI DATA")
Set wspr_pri = pr_pri.Worksheets("Feuil1")
Workbooks("PR_DETAIL(GD330_ANLDSV).xls").Activate
Workbooks("GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls").Activate
Workbooks("PR-PRI Check 2.2 GD330AT-00-V10o-204-XX-JAN-12-2010+0_PRI.xls").Activate
wspr_pri.Range("D13").Value = wspr.Range("D5")
wspr_pri.Range("E13").Value = wspr.Range("F9")
If wspr_pri.Range("D13").Value <> wspr_pri.Range("E13").Value Then
wspr_pri.Range("H13").Value = "NG"
Else: wspr_pri.Range("H13").Value.Value = "OK"
End If
End Sub

and now the prob was in ligne : wspr_pri.Range("H13").Value = "NG"

why????