dgmoore1
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
Does anybody know how I can troubleshoot this?
Thanks
Dave
ASKER
Because I was getting these odd errors I added this section of code for diagnostic purposes:
This seems to happen randomly - many of the cell values returned are correct, but several are not.
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
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.
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
rather than just this.
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
rather than just this.
Range("A1").Value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
This does the trick - many thanks!
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2013
Marty - MVP 2009 to 2013
Could you send part of the code where the problem is?
Regards