Macro help
 

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("D3:D721").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

Macro help
 

Try this version:
LastRowInC = Range("C" & Rows.Count).End(xlUp).Row
Range("D3:D" & 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:D" & 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("D3:D721").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

Macro help
 

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:D" & lr)
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"shaji" wrote in message
...
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("D3:D721").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

Macro help
 

it is working great.

thanks


"Stefi" wrote:

Try this version:
LastRowInC = Range("C" & Rows.Count).End(xlUp).Row
Range("D3:D" & 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:D" & 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("D3:D721").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