help modify code

[Don Doan]

Hi,
I'm a beginner VB and I'm trying to modify this code so that it would
subtract column H against column F and put the resutl in column K...it should
do that until there is a blank space in column D. I can't seemed to get it
working...it only does H2-F2 and not for all the rows...any idea??

Row = 1
With Sheets("Data")
Range("K2").Select
Do While .Range("D" & Row) <> ""
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-5]"
selection.NumberFormat = "dd h:mm:ss"
Row = Row + 1
Loop
End With

 

[George Nicholson]

...it only does H2-F2 and not for all the rows...any idea??
Yep. The ActiveCell/Selection never moves off of K2.

The following aircode doesn't rely on Selection or ActiveCell, which are
performance hogs. However, there are lots of ways to do this.

iRow = 2
With Sheets("Data")
Do While .Range("D" & iRow-1) <> ""
.Range("K" & iRow).FormulaR1C1 = "=RC[-3]-RC[-5]"
.Range("K" & iRow).NumberFormat = "dd h:mm:ss"
iRow = iRow + 1
Loop
End With

 

[JLGWhiz]

This should work.

With Sheets("Data")
Range("K2").Select
Do While .Range("D" & ActiveCell.Row) <> ""
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-5]"
selection.NumberFormat = "dd h:mm:ss"
ActiveCell.Offset(1, 0).Activate
Loop
End With

This will also do what you want.

With Sheets("Data")
lastRow = .Cells(Rows.Count, 4).End(xlUp).Row
.Range("K2:K" & lastRow).NumberFormat = "dd h:mm:ss"
End With

 

[Joel]

First, you can't use the variable ROW it is a reserved word. change to
RowCount. I simplified the code so it is easy to understand. You don't need
select and you don't need offsets.

RowCount = 1
With Sheets("Data")
Do While .Range("D" & RowCount) <> ""
Range("K" & RowCount).Formula = "=H" & RowCount & "-F" & RowCount
Range("K" & RowCount).NumberFormat = "dd h:mm:ss"
RowCount = RowCount + 1
Loop
End With

 

[Smallweed]

You need the cursor to move down a cell each time - the extra line below
should do it:

Row = 1
With Sheets("Data")
Range("K2").Select
Do While .Range("D" & Row) <> ""
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-5]"
activecell.offset(1,0).select
selection.NumberFormat = "dd h:mm:ss"
Row = Row + 1
Loop
End With