Hi Everyone, I have some VBA code that searches data in a large table from one sheet and copies specific from that table to another sheet based on a list. The problem that I'm having is when the data is a number, the script seems to fail even if I change the data to text. The VBA code is at the bottom. When you run this code, the first two tabs populate just fine but the third item in the troubled list causing the script to crash. What can I do to have the script match all values in the troubled list? I'd be happy to post the excel file but I don't seem to have that permission.
Sub troubled()
Dim sh As Worksheet, sSh As Worksheet, LR As Long, rng As Range, c As Range, fLoc As Range
Set sh = Sheets("Troubled Items") 'Edit sheet name
Set sSh = Sheets("Master") 'Edit sheet name
LR = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & LR)
For Each c In rng
Set fLoc = sSh.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fLoc Is Nothing Then
fAdr = fLoc.Address
Do
If Sheets(c.Value).Range("A17") = "" Then
fLoc.EntireRow.Copy Sheets(c.Value).Range("A17")
Else
fLoc.EntireRow.Copy Sheets(c.Value).Cells(Rows.Count, 1).End(xlUp)(2)
End If
fLoc.Value = c.Value
Set fLoc = sSh.Range("A:A").FindNext(fLoc)
Loop While fAdr <> fLoc.Address
End If
Next
End Sub
Thanks
Nick
Sub troubled()
Dim sh As Worksheet, sSh As Worksheet, LR As Long, rng As Range, c As Range, fLoc As Range
Set sh = Sheets("Troubled Items") 'Edit sheet name
Set sSh = Sheets("Master") 'Edit sheet name
LR = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & LR)
For Each c In rng
Set fLoc = sSh.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fLoc Is Nothing Then
fAdr = fLoc.Address
Do
If Sheets(c.Value).Range("A17") = "" Then
fLoc.EntireRow.Copy Sheets(c.Value).Range("A17")
Else
fLoc.EntireRow.Copy Sheets(c.Value).Cells(Rows.Count, 1).End(xlUp)(2)
End If
fLoc.Value = c.Value
Set fLoc = sSh.Range("A:A").FindNext(fLoc)
Loop While fAdr <> fLoc.Address
End If
Next
End Sub
Thanks
Nick
