| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement to fill in column to last row
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............ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement to fill in column to last row
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 -- Rick (MVP - Excel) "BEEJAY" wrote in message ... 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............ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement to fill in column to last row
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? "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "BEEJAY" wrote in message ... 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............ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement to fill in column to last row
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/libr...70(VS.60).aspx -- Rick (MVP - Excel) "BEEJAY" wrote in message ... 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? "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "BEEJAY" wrote in message ... 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............ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement to fill in column to last row
Rick:
Not THAT is an explanation that even I can understand. Thanks for everything. "Rick Rothstein" wrote: 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/libr...70(VS.60).aspx -- Rick (MVP - Excel) "BEEJAY" wrote in message ... 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? "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "BEEJAY" wrote in message ... 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............ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement to fill in column to last row
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. "BEEJAY" wrote: 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............ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fill Color If Statement | Excel Worksheet Functions | |||
| How to fill a cell using an IF statement | Excel Discussion (Misc queries) | |||
| How do you Control the fill color using an if statement formula? | Excel Worksheet Functions | |||
| How to fill a column with color in statement | Excel Programming | |||
| Fill an Indirect Statement | Excel Discussion (Misc queries) | |||
Hybrid Mode
