How to store the results of my for each loop If statement into another worksheet columns?

[Michael Ziegler]

I need to store the results of my for each If statement in the results worksheet of the active workbook.


My loop works but I need to be able to store the results in column a and c from the results worksheet


This is the code for the loop

 For Each cel In Range("A4:A85")

                With cel

                   If (.Value Like "boston*" Or .Value Like "manfield*" Or _
                .Value Like "barnes*" Or.Value Like "langley*") _
                And .Offset(0, 2).Value Like "mass*" Then



                     MsgBox cel.Value & Chr(13) & Chr(10) &  cel.Offset(0, 2).Value

I was thinking about something like this

Pseudocode: worksheets worksheetname column.value = cel.value and
cell.offset(0,2).value =cell.offset(0,2).value

Thanks

 

[offthelip]

I would always do this type of task using variant arrays because it is much much faster, if you only have few lines then it doesn't matter but if you have 1000 then variant array are at least 1000 time faster and more flexible:
Try this code (untested)

Sub faster()
inarr = Range("a4:C85") ' Note i have changed this to include column C so that we can test what was offset 2
Dim cola(1 To 81, 1)  ' Note 1 to 81 is the same size as 4 to 85
Dim colc(1 To 81, 1)  ' Note 1 to 81 is the same size as 4 to 85
indi = 1
For i = 1 To 81
                     If (inarr(i, 1) Like "boston*" Or inarr(i, 1) Like "manfield*" Or _
                inarr(i, 1) Like "barnes*" Or inarr(i, 1) Like "langley*") _
                And inarr(i, 3) Like "mass*" Then
                 cola(indi, 1) = inarr(i, 1)
                 colc(indi, 1) = inarr(i, 3)
                 indi = indi + 1 ' increment the out index
                End If
Next i
With Worksheets("Results")
 Range(.Cells(1, 1), .Cells(81, 1)) = cola ' write the varaint array back to column A results worksheet
 Range(.Cells(1, 3), .Cells(81, 3)) = cola ' write the varaint array back to  Column C results worksheet
End With
                 
End Sub

 

[Michael Ziegler]

Thanks, It looks good I'll give a try.

 

[offthelip]

I have just spotted an error in my code ( due to cut and paste):

With Worksheets("Results")
 Range(.Cells(1, 1), .Cells(81, 1)) = cola ' write the varaint array back to column A results worksheet
 Range(.Cells(1, 3), .Cells(81, 3)) = [COLOR=#ff0000]colc[/COLOR] ' write the varaint array back to  Column C results worksheet
End With

 

[Michael Ziegler]

Thanks, I really appreciate your help