Search folder for value and list left/right value

[JJCam]

Dear all,

I need to resort to your wisdom as I have been struggling half day to work this VBA out.
I'm a total amateur in VBA and always resort to already posted codes, so apologies for my ignorance.
I need to search in a folder (with subfolders containing several files, including xlsm files with several sheets) a specific value.
I would like to see the contents of the row where that value is.
As an example, let's say I have a full set of excel files with payments and I need to search for the value "creditcard" and see the which excel file contains that value and list also the row (to be able to see the cost and date, for example)

As it was too complicated for me, I ended up "cleaning" the folder and now I only have a single folder with several xlsm files (only containing a single sheet).

I have the following code working,, but what it's been driving me crazy is the "(xlToLeft).Value". It works fine, BUT if I change to xlToRight it doesn't show the neighboring value. Why???

Sub SearchValue()

Dim strFirstAddress As String
Dim strSearch As String
Dim strExtension As String
Dim wOut As Worksheet
Dim wkbSource As Workbook
Dim wks As Worksheet
Dim rFound As Range
Dim lRow As Long
Const strPATH As String = "/Users/temp/" 'Folder to search

ChDir strPATH
strExtension = Dir("*.xlsm*") 'File type to search
strSearch = InputBox("Please enter the Search Keyword.") 'Search Keyword all reports
Set wOut = Worksheets.Add
wOut.Range("A1:D1") = Array("Workbook", "Workseet", "Cell", "Text in Cell")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPATH & strExtension)
With wkbSource
For Each wks In .Sheets
Set rFound = wks.UsedRange.Find(strSearch, LookIn:=xlValues, lookat:=xlWhole)
If Not rFound Is Nothing Then
strFirstAddress = rFound.Address
Do
lRow = wOut.Cells(Rows.Count, 1).End(xlUp).Row + 1
wOut.Cells(lRow, 1) = wkbSource.Name
wOut.Cells(lRow, 2) = wks.Name
wOut.Cells(lRow, 3) = rFound.Address
wOut.Cells(lRow, 4) = rFound.Value
wOut.Cells(lRow, 5) = wks.Cells(rFound.Row, Columns.Count).End(xlToLeft).Value
Set rFound = wks.UsedRange.FindNext(rFound)
Loop While rFound.Address <> strFirstAddress
sAddr = ""
End If
Next wks
End With
wkbSource.Close savechanges:=False
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub

If someone could enlighthen me with the logic. I really cannot understand why it works and shows the value on the right (when I use xlToLeft) but it doesn't work at all when I use xlToRight.

Thanks!

JJCam

 

[igold]

Hi JJCam, welcome to the MrExcel Forum.
Not 100% sure, but I think it could be that when you go to the left, when it hits column 1 it wraps around to the very last column and then keeps working it's way to the left, from the last column to your starting point. While if it goes to the right, it starts at your starting point and goes to the right and stops at the last column. If your desired result is to the left of the starting point it does not wrap around after the last column to continue searching to the right from column 1.

 

[JJCam]

Hi JJCam, welcome to the MrExcel Forum.
Not 100% sure, but I think it could be that when you go to the left, when it hits column 1 it wraps around to the very last column and then keeps working it's way to the left, from the last column to your starting point. While if it goes to the right, it starts at your starting point and goes to the right and stops at the last column. If your desired result is to the left of the starting point it does not wrap around after the last column to continue searching to the right from column 1.

Thanks, it sort of makes sense. I guess it wasn't probably the best code for my requirement. I'll have to think of a different approach to be able to show the values of the row of the search keyword.