Hi All,
so basically i have the following table [TABLE="width: 351"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Depart[/TD]
[TD="align: center"]Month[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]01/11/2019[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]01/12/2019[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]19/12/2019[/TD]
[TD="align: center"]December[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]19/12/2019[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]14/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]25/12/2019[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]30/12/2019[/TD]
[TD="align: center"]December[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]30/11/2019[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]12/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]21/12/2019[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]02/11/2019[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]21/11/2019[/TD]
[TD="align: center"]November[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]15/11/2019[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]12/12/2019[/TD]
[TD="align: center"]December[/TD]
[/TR]
</tbody>[/TABLE]
so im currently using the following Vba to filter the data
Range("A1:D9").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$9").AutoFilter Field:=3, Criteria1:= _
">12/31/2019", Operator:=xlAnd
which leaves me with the following
[TABLE="width: 351"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Depart[/TD]
[TD="align: center"]Month[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]01/11/2019[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]19/12/2019[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]14/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]30/11/2019[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]12/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]21/12/2019[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
</tbody>[/TABLE]
so what im actually after is a vba code to amend the value in the current visible cells in column D from January to 2020, is this possible to do in the one sheet or would i need to copy the current visible data to new sheet make the amends then remove the original data and paste this over
thanks for any help given in advance!
so basically i have the following table [TABLE="width: 351"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Depart[/TD]
[TD="align: center"]Month[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]01/11/2019[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]01/12/2019[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]19/12/2019[/TD]
[TD="align: center"]December[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]19/12/2019[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]14/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]25/12/2019[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]30/12/2019[/TD]
[TD="align: center"]December[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]30/11/2019[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]12/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]21/12/2019[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]02/11/2019[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]21/11/2019[/TD]
[TD="align: center"]November[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]15/11/2019[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]12/12/2019[/TD]
[TD="align: center"]December[/TD]
[/TR]
</tbody>[/TABLE]
so im currently using the following Vba to filter the data
Range("A1:D9").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$9").AutoFilter Field:=3, Criteria1:= _
">12/31/2019", Operator:=xlAnd
which leaves me with the following
[TABLE="width: 351"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Depart[/TD]
[TD="align: center"]Month[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]01/11/2019[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]19/12/2019[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]14/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]30/11/2019[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]12/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]21/12/2019[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]01/01/2020[/TD]
[TD="align: center"]January[/TD]
[/TR]
</tbody>[/TABLE]
so what im actually after is a vba code to amend the value in the current visible cells in column D from January to 2020, is this possible to do in the one sheet or would i need to copy the current visible data to new sheet make the amends then remove the original data and paste this over
thanks for any help given in advance!