troubleshooting Question

Excel vba find xlvalues not working as expected

Avatar of CraigStenton
CraigStenton asked on
Microsoft Excel
32 Comments1 Solution489 ViewsLast Modified:
I am working on a spreadsheet, which has lots of formulas that look up data from other worksheets and I now need to copy the resulting values to another worksheet.  
I have used the following code, which works correctly on my test workbook where it correctly finds the last resulting value despite there being formulas further down.
LastRow = Cells.Find(What:="*", after:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, LookIn:=xlValues).Row
      'Search for any entry, by searching backwards by Columns.
                  LastColumn = Cells.Find(What:="*", after:=[A1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, LookIn:=xlValues).Column
                tlastrow = tlastrow + LastRow

However, when I transfer the code to the live workbook, it doesn't correctly find the last value.  The cell that it finds has no data at all along the row (other than a formula) and I cannot see any reason for it finding the cell that it does, other than possible formatting issues. There is a definite difference in the way in which the find function is working between the two spreadsheets
I will send both spreadsheets directly as required.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 32 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 32 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros