Hi.

Can anyone tell me what is wrong with this code? I know I've used an IF
statement in the past, but code fills in formula for all rows without IF
statement. As soon as I add IF statement, stops working entirely. Thanks,
in advance.

Sub trynext()

Sheets("Sheet3").Select
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
With ActiveSheet
For rowi = 2 To LastRow
If Cells(i, 2) = "" Then
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"
End If
Next
End With
End Sub

i is undefined.
--
Gary''s Student - gsnu200902


"hnyb1" wrote:

Hi.

Can anyone tell me what is wrong with this code? I know I've used an IF
statement in the past, but code fills in formula for all rows without IF
statement. As soon as I add IF statement, stops working entirely. Thanks,
in advance.

Sub trynext()

Sheets("Sheet3").Select
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
With ActiveSheet
For rowi = 2 To LastRow
If Cells(i, 2) = "" Then
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"
End If
Next
End With
End Sub

Sub trynextSAS()
With Sheets("sheet3")
lr = .Cells(Rows.Count, 3).End(xlUp).Row
For i = 2 To lr
If .Cells(i, 2) = "" Then
.Cells(i, "b") = "4/28/2009"
.Cells(i, "e") = 1
End If
Next
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"hnyb1" wrote in message
...
Hi.

Can anyone tell me what is wrong with this code? I know I've used an IF
statement in the past, but code fills in formula for all rows without IF
statement. As soon as I add IF statement, stops working entirely.
Thanks,
in advance.

Sub trynext()

Sheets("Sheet3").Select
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
With ActiveSheet
For rowi = 2 To LastRow
If Cells(i, 2) = "" Then
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"
End If
Next
End With
End Sub

this line

If Cells(i, 2) = "" Then

seems like it should be

If Cells(rowi, 2) = "" Then

in addition, this code:
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"

could be shortened up by removing the selects:
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Formula = "1"

hth
:)
susan



On Sep 9, 9:04*am, hnyb1 wrote:
Hi.

Can anyone tell me what is wrong with this code? *I know I've used an IF
statement in the past, but code fills in formula for all rows without IF
statement. *As soon as I add IF statement, stops working entirely. *Thanks,
in advance.

Sub trynext()

* * Sheets("Sheet3").Select
* * LastRow = Cells(Rows.Count, 3).End(xlUp).Row
* * With ActiveSheet
* * * * For rowi = 2 To LastRow
* * * * If Cells(i, 2) = "" Then
* * * * * * Cells(rowi, "b").Select
* * * * * * Cells(rowi, "b").Formula = "4/28/2009"
* * * * * * Cells(rowi, "e").Select
* * * * * * Cells(rowi, "e").Formula = "1"
* * * * End If
* * * * Next
* * * * End With
End Sub

Thanks so much! That was exactly it!!!

"Susan" wrote:

this line

If Cells(i, 2) = "" Then

seems like it should be

If Cells(rowi, 2) = "" Then

in addition, this code:
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"

could be shortened up by removing the selects:
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Formula = "1"

hth
:)
susan



On Sep 9, 9:04 am, hnyb1 wrote:
Hi.

Can anyone tell me what is wrong with this code? I know I've used an IF
statement in the past, but code fills in formula for all rows without IF
statement. As soon as I add IF statement, stops working entirely. Thanks,
in advance.

Sub trynext()

Sheets("Sheet3").Select
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
With ActiveSheet
For rowi = 2 To LastRow
If Cells(i, 2) = "" Then
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"
End If
Next
End With
End Sub

Why select. Did you look at mine

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"hnyb1" wrote in message
...
Thanks so much! That was exactly it!!!

"Susan" wrote:

this line

If Cells(i, 2) = "" Then

seems like it should be

If Cells(rowi, 2) = "" Then

in addition, this code:
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"

could be shortened up by removing the selects:
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Formula = "1"

hth
:)
susan



On Sep 9, 9:04 am, hnyb1 wrote:
Hi.

Can anyone tell me what is wrong with this code? I know I've used an
IF
statement in the past, but code fills in formula for all rows without
IF
statement. As soon as I add IF statement, stops working entirely.
Thanks,
in advance.

Sub trynext()

Sheets("Sheet3").Select
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
With ActiveSheet
For rowi = 2 To LastRow
If Cells(i, 2) = "" Then
Cells(rowi, "b").Select
Cells(rowi, "b").Formula = "4/28/2009"
Cells(rowi, "e").Select
Cells(rowi, "e").Formula = "1"
End If
Next
End With
End Sub