Find a specific row in an Excel workbook
So, I need to make an Excel macro in VBA that will search for a string and then compare it to a predefined string of my selection, then change the value of a cell in another sheet.
It works like this:
Sub Macro1()
Dim A As Integer
Dim WS As Worksheet
Dim ToCompare, Coniburo As String
Coniburo = "My String"
For Each WS In Worksheets
For A = 1 To Rows.Count
ToCompare = Left(Cells(A, 3), 100)
If InStr(ToCompare, Coniburo) > 0 Then
Sheets("Last Sheet").Cells(21, 2).Value = "233"
End If
Next A
Next
The macro works ....... If I remove the first For (the one that searches through sheets) and while I'm on the sheet where "My row" is present. Otherwise it won't work. It takes a long time to process, in a minute, since there are 17 sheets.
Why does not it work? I've read a lot of posts here, the Microsoft Dev forum, a site called Tech on the Net, and there is still a little something I am missing, but I don't know why.
Can anyone point me in the right direction?
source to share
Use the With ... End With command to focus the parent sheet for each iteration of the loop.
Option Explicit
Sub Macro1()
Dim a As Long, Coniburo As String, ws As Worksheet
Coniburo = "My String"
For Each ws In Worksheets
With ws
For a = 1 To .Cells(.Rows.Count, "C").End(xlUp).Row
If CBool(InStr(Left(.Cells(a, 3), 100), Coniburo, vbTextCompare)) Then
Worksheets("Last Sheet").Cells(21, 2).Value = 233
End If
Next a
End With
Next
End Sub
You need to prefix the calls to strings, ranges and cells with a period like .Rows...
or .Range(...)
or .Cells(...)
when inside with ... End With block. This identifies them with the parent sheet described with With .. End With.
I also made the comparison case insensitive with vbTextCompare
.
There remains the problem of writing and overwriting 233 to the same cell on the same sheet, but that's a different matter.
source to share
I've been looking around for the rules a bit, but I want to show you how we can use the built-in FIND function to speed things up dramatically. We'll just process each sheet in column C only; we will use FIND function to find the ROW number where column C contains your search string .... then we will check that cell to see if your search string is within the first 100 characters as per your requirement. If so, we will consider it a coincidence. In addition to your "233" registration result on the "Last Page" sheet, I've turned on a bright green highlight to help you understand what's going on ...
Sub findConiburo()
Coniburo = "My String"
For Each ws In Worksheets
With ws.Range("C:C")
myName = ws.Name 'useful for debugging
queue = 1 'will be used to queue the FIND function
x = 0 'loop counter
Do 'loop to find multiple results per sheet
On Error Resume Next 'Disable error handling
'FIND Coniburo within ws column C, log row number:
'Note ".Cells(queue, 1)" is a relative reference to the current WS, column C
foundRow = .Find(What:=Coniburo, After:=.Cells(queue, 1), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
'If no result found then an error number is stored. Perform error handling:
If Err.Number <> 0 Then
'No results found, don't do anything, exit DO to skip to next sheet:
Exit Do
End If
On Error GoTo 0 'Re-enable error handling
If x = 0 Then
'first loop - log the first row result:
originalFoundRow = foundRow
ElseIf foundRow = originalFoundRow Then
'Not the first loop. Same result as original loop = we're back at the start, so exit loop:
Exit Do
End If
'Update queue so next loop will search AFTER the previous result:
queue = foundRow
'check if the string is not only SOMEWHERE in the cell,
'but specifically within the first 100 characters:
ToCompare = Left(.Cells(foundRow, 1), 100)
If InStr(ToCompare, Coniburo) > 0 Then
.Cells(foundRow, 1).Interior.ColorIndex = 4 'highlight green
Sheets("Last Sheet").Cells(21, 2).Value = "233"
End If
'Update loop counter:
x = x + 1
Loop
End With
Next ws
End Sub
source to share