-
Currently I'm only search 3 columns I go to a fourth but no more than 4
I tried adding Others to search list and put data into sheet others2.
Your code worked perfectly for search the 2 columns but when I added a third I did something wrong. I keep getting a error.
Below is what code I have and I made BOLD and Underlined what the debugger is saying is the issue.
Another question I have is are my sheet names and my list names to similar that it is making vba work harder?
I also unmerged cell a and b on the recipe sheet.
Thanks Again
Sub MoveData()
Sheets("Grain2").Cells.ClearContents
Sheets("Hop2").Cells.ClearContents
Sheets("Other2").Cells.ClearContents
DestnColumn = 1
For Each are In Sheets("Recipe").Columns(1).SpecialCells(xlCellTypeConstants, 2).Areas
'are.Select
GrainDestnRow = 1: HopDestnRow = 1
For Each cll In are.Columns(1).Cells 'deals with he merged cells (try not to use merged cells with VBA - they're very hard work!)
'cll.Select
CurrentItem = Application.Trim(cll.Value)
Set GrainFound = Sheets("Inputs").Columns(1).Find(what:=CurrentItem, lookat:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
If Not GrainFound Is Nothing Then
Sheets("Grain2").Cells(GrainDestnRow, DestnColumn) = CurrentItem
GrainDestnRow = GrainDestnRow + 1
End If
Set HopsFound = Sheets("Inputs").Columns(3).Find(what:=CurrentItem, lookat:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
If Not HopsFound Is Nothing Then
Sheets("Hop2").Cells(HopDestnRow, DestnColumn) = CurrentItem
HopDestnRow = HopDestnRow + 1
End If
Set OthersFound = Sheets("Inputs").Columns(5).Find(what:=CurrentItem, lookat:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
If Not OthersFound Is Nothing Then
Sheets("Other2").Cells(OthersDestnRow, DestnColumn) = CurrentItem
OthersDestnRow = OthersDestnRow + 1
End If
Next cll
DestnColumn = DestnColumn + 1
Next are
End Sub
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules