Macro creating zero entries

[johngio]

Hi all,

I have created a macro which looks for the letters f or l in the AA column. If once of these letters appears, I would like this to appear in either the Y (if the value is f) or Z (if the value is l) column:

6657
6657
6657
4029

However, rather than the numbers being inputted down 4 rows, I end up with a zero in the column in line with the l or f, and no numbers below it.

Here is my code:

Sub Macro5()
For iRow = 15 To 279
If Cells(iRow, 27) = "f" Then
Cells(iRow, 25) = 6657 And Cells(iRow + 1, 25) = 6657 And Cells(iRow + 2, 25) = 6657 And Cells(iRow + 3, 25) = 4029
ElseIf Cells(iRow, 27) = "l" Then
Cells(iRow, 26) = 6657 And Cells(iRow + 1, 26) = 6657 And Cells(iRow + 2, 26) = 6657 And Cells(iRow + 3, 26) = 4029

End If
Next iRow
End Sub

Any ideas?

Cheers

John

 

[Andrew Poulsom]

You can't use a logical And like that. Try:

Sub Macro5() 
   For iRow = 15 To 279 
      If Cells(iRow, 27) = "f" Then 
         Cells(iRow, 25) = 6657
         Cells(iRow + 1, 25) = 6657
         Cells(iRow + 2, 25) = 6657
         Cells(iRow + 3, 25) = 4029 
      ElseIf Cells(iRow, 27) = "l" Then 
         Cells(iRow, 26) = 6657
         Cells(iRow + 1, 26) = 6657
         Cells(iRow + 2, 26) = 6657
         Cells(iRow + 3, 26) = 4029 
      End If 
   Next iRow 
End Sub

Or use the colon between the assignment statements, eg:

Cells(iRow, 25) = 6657: Cells(iRow + 1, 25) = 6657: Cells(iRow + 2, 25) = 6657: Cells(iRow + 3, 25) = 4029

 

[johngio]

Thats what I was after!!

Thanks!