Macro help

[Guest]

Sir,

I am getting daily an excel file with data which varies every day. I have
recorded a macro to check the data which is given below:
Sub checktime()
Range("D1").Select
Selection.EntireColumn.Insert
Range("D1").Select
ActiveCell.FormulaR1C1 = "TTime"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=AND(RC[-1]>=--LEFT(RC[8],5),RC[-1]<=--RIGHT(RC[8],5))"
Range("D2").Select
Selection.Copy
Range("D3721").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D1").Select
End Sub

my problem is that, I want to paste the copied cell("D2") down till the row
in which there is data in column C.
please provide a modification in the code to accommodate the the above.

thanks

 

[Guest]

Try this version:
LastRowInC = Range("C" & Rows.Count).End(xlUp).Row
Range("D3" & LastRowInC).Select


Sub checktime()
Range("D1").Select
Selection.EntireColumn.Insert
Range("D1").Select
ActiveCell.FormulaR1C1 = "TTime"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=AND(RC[-1]>=--LEFT(RC[8],5),RC[-1]<=--RIGHT(RC[8],5))"

LastRowInC = Range("C" & Rows.Count).End(xlUp).Row

Range("D2").Select
Selection.Copy

Range("D3" & LastRowInC).Select

ActiveSheet.Paste
Application.CutCopyMode = False
Range("D1").Select
End Sub

Regards,
Stefi


„shaji†ezt írta:

 

[Don Guillett]

try this
sub copydo()
lr=cells(rows.count,"c").end(xlup).row
range("d1")="TTime"
Range("D2").FormulaR1C1 = _
"=AND(RC[-1]>=--LEFT(RC[8],5),RC[-1]<=--RIGHT(RC[8],5))"
Range("D2").copy Range("D3" & lr)
end sub

 

[Guest]

it is working great.

thanks

Try this version:
LastRowInC = Range("C" & Rows.Count).End(xlUp).Row
Range("D3" & LastRowInC).Select


Sub checktime()
Range("D1").Select
Selection.EntireColumn.Insert
Range("D1").Select
ActiveCell.FormulaR1C1 = "TTime"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=AND(RC[-1]>=--LEFT(RC[8],5),RC[-1]<=--RIGHT(RC[8],5))"

LastRowInC = Range("C" & Rows.Count).End(xlUp).Row

Range("D2").Select
Selection.Copy

Range("D3" & LastRowInC).Select

ActiveSheet.Paste
Application.CutCopyMode = False
Range("D1").Select
End Sub

Regards,
Stefi


„shaji†ezt írta:

Sir,

I am getting daily an excel file with data which varies every day. I have
recorded a macro to check the data which is given below:
Sub checktime()
Range("D1").Select
Selection.EntireColumn.Insert
Range("D1").Select
ActiveCell.FormulaR1C1 = "TTime"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=AND(RC[-1]>=--LEFT(RC[8],5),RC[-1]<=--RIGHT(RC[8],5))"
Range("D2").Select
Selection.Copy
Range("D3721").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D1").Select
End Sub

my problem is that, I want to paste the copied cell("D2") down till the row
in which there is data in column C.
please provide a modification in the code to accommodate the the above.

thanks