Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Why do I get a Run-time error 91 when debugging a cell value in my VBA Excel application ?

Hi Experts,

I have a VBA application which loops through the cells in a column in Excel. Each cell is tested for a particular value - ="N". When I run the application it seems to ignore this test and carries on regardless.

When debugging the code I get a 'Run-time error 91 Object variable or With block variable not set ' message, even though I've Set the sheet object.

I've attached the code below.

   Dim wbInput              As Workbook
   Dim wsInputSource   As Worksheet
   Dim sFileName          As String

   On Error Resume Next
   
' OPEN THE REQUIRED WORKBOOK
   
   sFileName = Application.GetOpenFilename
   Set wbInput = Workbooks.Open(sFileName)

' TEST IF NEW BUSINESS
   
            Application.ScreenUpdating = False

            Set wsInputSource = wbInput.Sheets("Manchester")

            For r = 2 To 50
               If wsInputSource.Cells(r, 2).Value = "N" Then
                     If wsInputSource.Cells(r, 3) <> 0 Then
                        wsInputSource.Cells(r, 4).value = "New"
                     End If
               End If
            Next r
         

...etc etc....

Open in new window



Any ideas
Thanks
Toco
0
Tocogroup
Asked:
Tocogroup
  • 3
  • 3
  • 3
2 Solutions
 
Rory ArchibaldCommented:
Start by commenting out the On Error Resume Next. It's generally a bad idea to just stick that at the top of a routine.
0
 
Phillip BurtonCommented:
1. Where does it breakdown? Which line number?
2. I'm assuming that the workbook you are opening actually has a spreadsheet called "Manchester".
0
 
Phillip BurtonCommented:
Line 20 doesn't use ".Value" - I don't know if that is significant or not.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
TocogroupAuthor Commented:
Ok......I've commented out the On Error line.

It's now returning a 'Run-Time error 1004 Application-defined or object-defined error' on the sFilename = Application.GetOpenFilename statement. I'm prompted to enter a password, which I do, and then it falls over.

Yes, the sheet 'Manchester' exists.
0
 
Phillip BurtonCommented:
Does it work if the workbook is not password protected?
0
 
Rory ArchibaldCommented:
Are you sure it's on that line and not the next one? GetOpenFileName shouldn't prompt you for a password as it's not opening anything.
0
 
TocogroupAuthor Commented:
Thanks both,

I managed to get it through the open file procedure. The problem was the sheet filename: the creator had addeda space to the end of Manchester.
I'm going to split the points on this one because I've learned two things:
1. Comment out the On Error statement when debugging.
2. Always check the sheet name for hidden characters

Much appreciated
Toco
0
 
Rory ArchibaldCommented:
#1 should be "Don't just put 'On Error Resume Next' at the top of a procedure. Use it only where required.

:)
0
 
TocogroupAuthor Commented:
Thanks Rory. Point taken
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now