Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

excel vba

  Asked By: Molly    Date: Oct 02    Category: MS Office    Views: 824
  

Why do I get "Run-Time error '1004': Application-defined or Object-
defined error" when using an array variable:

Option Explicit
Public rIndex As Integer
Sub main()
For rIndex = 0 To 17
If Worksheets("Sheet2").Cells(rIndex, 1).Value = "EE16" Then
MsgBox Worksheets("Sheet2").Cells(7, 2) & Worksheets("Sheet2").Cells
(7, 3)
Next
End Sub

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Myron James     Answered On: Oct 02

Your loop control variable (rIndex) runs from 0 to whatever. When it
is 0 and is used in ...cells(rIndex,1).. it fails because there is no
row 0 on a spreadsheet. Start from 1 instead.

 
Answer #2    Answered By: Vidisha Pathak     Answered On: Oct 02

try putting .value after each of the cells in the msgbox statement.. if
you want the value of those cells to be in the message box...

 
Answer #3    Answered By: Barney Smith     Answered On: Oct 02

This is b'coz the Cells index can't be cells(0,1) which you have defined as
Cells(rIndex, 1) where rIndex is 0
chnage the r index to 1 to 17 it will work

 
Answer #4    Answered By: Bu Nguyen     Answered On: Oct 02

Revise your code to this.

Option Explicit
Sub main()
Public rIndex As Integer
For rIndex = 1 To 17
If Worksheets("Sheet2").Cells(rIndex, 1).Value = "EE16" Then
MsgBox Worksheets("Sheet2").Cells(7, 2) &
Worksheets("Sheet2").Cells(7, 3)
End If
Next rlndex
End Sub

 
Answer #5    Answered By: Alonzo Roberts     Answered On: Oct 02

There is no row zero. Start from row 1.

 
Didn't find what you were looking for? Find more on excel vba Or get search suggestion and latest updates.




Tagged: