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
dgmoore1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

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

Regards
0
dgmoore1Author Commented:
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.
0
Martin LissOlder than dirtCommented:
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

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Martin LissOlder than dirtCommented:
So in your code

Set CurrXLS = Xl.Workbooks.Open(f)
Xl.Visible = True
Dim I As Integer
  With CurrXLS
  For I = 1 To 30
    rng = "A" & I
    With Sheets("Sheet1")
        ' Added a period before Range
        MsgBox I & " - " & .Range(rng)
    End With
  Next I

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgmoore1Author Commented:
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!
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.