asked on

Excel vba validation error

I am using VBA in Access to validate Excel files by checking the values of certain cells. This is working, but sporadically. In some cases the VBA is returning values that are different from the actual values of the cells. For example, Range("B30") may be null, but the vba returns a value that does not exist anywhere in the spreadsheet; or Range("A16") may contain "Program Manager", but the vba returns "Consultant", which also does not exist anywhere in the spreadsheet (except in a lookup list on a hidden sheet).

Does anybody know how I can troubleshoot this?

Thanks
Dave

Rgonzo1971

Hi,

Could you send part of the code where the problem is?

Regards

dgmoore1

ASKER

Because I was getting these odd errors I added this section of code for diagnostic purposes:

Set CurrXLS = Xl.Workbooks.Open(f)
Xl.Visible = True
Dim I As Integer
  With CurrXLS
  For I = 1 To 30
    rng = "A" & I
    MsgBox I & " - " & Range(rng)
  Next I

Open in new window

When rng = "A14", the value in the spreadsheet is "Program Manager", but the VBA returns "Consultant", which does not exist anywhere in the sheet. When rng = "B31", the value in the sheet is null, but the VBA returns "26,500", which also does not exist anywhere in the sheet.
This seems to happen randomly - many of the cell values returned are correct, but several are not.

Martin Liss

Your VBA code is most likely just referring to a Range or a Cell without specifying the sheet on which the Range or Cell resides so the information is coming from some other sheet. To correct the problem you can do

With Sheets("Sheet1")
    ' this is just one line of what could be a whole block of code that uses values from Sheet1
    MyVar = .Range("A1").Value
End With

Open in new window

rather than just this.

Range("A1").Value

Open in new window

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dgmoore1

ASKER

I actually thought about using explicit references when I wrote the code, but apparently it flew out of my mind.

This does the trick - many thanks!

Martin Liss

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013