XcelNoobster
New Member
- Joined
- Jun 7, 2022
- Messages
- 40
I have a macro that loops through IDs and outputs the location including sheet name and then entire row where the value is found.
I also want to output the headers of the sheet where value is found above the entire row.
How would I modify my macro to accomplish this?
I also want to output the headers of the sheet where value is found above the entire row.
How would I modify my macro to accomplish this?
VBA Code:
Sub findIDs2()
' findIDs2 Macro
' Once Parsed1 is run, run findIDs2 macro to output location and entire line where that Id is found.
'
Application.ScreenUpdating = False
Dim srcRng As Range, rng As Range, sAddr As String, fnd As Range, ws As Worksheet, x As Long: x = 1
Dim rngWs As Range
Set srcRng = Sheets("IDs").Range("A2", Sheets("IDs").Range("A" & Rows.Count).End(xlUp))
For Each rng In srcRng
For Each ws In Sheets
If ws.Name <> "IDs" And ws.Name <> "Results" Then
Set fnd = ws.Cells.Find(rng, LookIn:=xlValues, lookat:=xlPart)
If Not fnd Is Nothing Then
sAddr = fnd.Address
Do
With Sheets("Results")
.Range("A" & x) = fnd
.Range("B" & x) = fnd.Address
.Range("C" & x) = ws.Name
Set rngWs = Intersect(fnd.EntireRow, fnd.CurrentRegion)
rngWs.Copy
.Range("D" & x).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
x = x + 1
End With
Set fnd = ws.Cells.FindNext(fnd)
Loop While fnd.Address <> sAddr
sAddr = ""
End If
End If
Next ws
Next rng
Application.ScreenUpdating = True
End Sub