asked on 9/11/2013

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

Microsoft Excel

Last Comment

Martin Liss

9/11/2013

Rgonzo1971

9/11/2013

Hi,

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

Regards

dgmoore1

9/11/2013

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

9/11/2013

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.