If Statement to fill in column to last row

B

BEEJAY

Frequent (large) downloads, into Excel, requires the addition of a column,
that is filled by the use of an "IF" statement.

The following If statement works fine, when tested.
How to get it to work thru all cells, to last row.

Dim lastRow As Long
lastRow = Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

If Range("C2") = "S" Or Range("C2") = "B" Then
Range("F2").Value = Range("D2").Value
Else
Range("F2") = 0
End If
End Sub

If there is a better, more efficient way, please also advise............
 
R

Rick Rothstein

Maybe this?

Dim X As Long
Dim lastRow As Long
lastRow = Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

For X = 2 To lastRow
If Cells(X, "C").Value = "S" Or Cells(X, "C").Value = "B" Then
Cells(X, "F").Value = Cells(X, "D").Value
Else
Cells(X, "F").Value = 0
End If
Next
 
J

Joel

the column you use to find the last row must be a column that contains data
in the last row. You picked column C in this case. Most worksheets have a
least one column that is always contains data. problems occur if you have
columns where there isn't data in every row.
 
B

BEEJAY

Rick:
Thanks so much. Tried it out on my test sheet - works as expected/wanted.

IF I could trouble you again, could you explain the what and how of the X =2?
 
B

BEEJAY

Thanks for the reminder.
Sometimes we try so hard, then miss something so basic.
 
R

Rick Rothstein

The variable X serves as a counter, or maybe "indexer" is a better word, a
loop. The For statement (which is always coupled with a Next statement)
forms a loop. The X=2 establishes a starting value for the indexer and the
value in the lastRow variable sets the ending value for it. So, X will take
on the values (inside the loop's code) of 2, 3, 4, etc. until it reaches the
value in the lastRow variable (at which time the loop will end). I used X=2
as the starting point for the loop because the code you posted seemed to
suggest that Row 2 is where you tried to start your code at. Here is an
online link to the help file for the For..Next statement...

http://msdn.microsoft.com/en-us/library/aa243370(VS.60).aspx
 
B

BEEJAY

Rick:
Not THAT is an explanation that even I can understand.
Thanks for everything.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top