Dear All,

I am looking for some help on a macro, I have a range of cells which require
to go forward 7 days, the code I have below works fine for the week
commencing
cell, the other cells are Days of week cells displayed as Monday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Here is the code:

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue
End Sub

I am also looking to put an extra code in so that all data in a range of
cells can be deleted and no fill colour, could anyone help me with this query.

Can anyone give me some help on this.

Kind Regards

Terilad

Adjust Range("B5:Z37") as your requirement..

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue

Range("B5:Z37").ClearContents
Range("B5:Z37").ClearComments
Range("B5:Z37").ClearFormats

End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Dear All,

I am looking for some help on a macro, I have a range of cells which require
to go forward 7 days, the code I have below works fine for the week
commencing
cell, the other cells are Days of week cells displayed as Monday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Here is the code:

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue
End Sub

I am also looking to put an extra code in so that all data in a range of
cells can be deleted and no fill colour, could anyone help me with this query.

Can anyone give me some help on this.

Kind Regards

Terilad

Hi Jacob,

Thanks for your help, reposted as I had no response from post yesterday.
Code clears contents but it also clears all the borders aswell, I need to
keep borders on sheet, only clear cell data and return to no fill colour.

Also I mentioned too about another range of cells that need to go forward 7
days have you any ideas how to implement this into code.

Many thanks


Terilad

"Jacob Skaria" wrote:

Adjust Range("B5:Z37") as your requirement..

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue

Range("B5:Z37").ClearContents
Range("B5:Z37").ClearComments
Range("B5:Z37").ClearFormats

End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Dear All,

I am looking for some help on a macro, I have a range of cells which require
to go forward 7 days, the code I have below works fine for the week
commencing
cell, the other cells are Days of week cells displayed as Monday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Here is the code:

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue
End Sub

I am also looking to put an extra code in so that all data in a range of
cells can be deleted and no fill colour, could anyone help me with this query.

Can anyone give me some help on this.

Kind Regards

Terilad

Try the below..please remove the earlier code..

Range("B5:Z37").ClearContents
Range("B5:Z37").Selection.Interior.ColorIndex = xlNone

and for the dates in D4,F4,H4,J4,L4,N4 and P4

Range("D4") = Range("D4") + 7
Range("F4") = Range("F4") + 7
and so on

If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Hi Jacob,

Thanks for your help, reposted as I had no response from post yesterday.
Code clears contents but it also clears all the borders aswell, I need to
keep borders on sheet, only clear cell data and return to no fill colour.

Also I mentioned too about another range of cells that need to go forward 7
days have you any ideas how to implement this into code.

Many thanks


Terilad

"Jacob Skaria" wrote:

Adjust Range("B5:Z37") as your requirement..

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue

Range("B5:Z37").ClearContents
Range("B5:Z37").ClearComments
Range("B5:Z37").ClearFormats

End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Dear All,

I am looking for some help on a macro, I have a range of cells which require
to go forward 7 days, the code I have below works fine for the week
commencing
cell, the other cells are Days of week cells displayed as Monday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Here is the code:

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue
End Sub

I am also looking to put an extra code in so that all data in a range of
cells can be deleted and no fill colour, could anyone help me with this query.

Can anyone give me some help on this.

Kind Regards

Terilad

Thanks, cells code work thankyou.

I am getting an error with the following line

Range("B5:Z37").Selection.Interior.ColorIndex = xlNone

When I remove this line from the code I get no error but the cells still
remain filled with colour, any other ideas?

Error is object doesn't support this property or method

Terilad

"Jacob Skaria" wrote:

Try the below..please remove the earlier code..

Range("B5:Z37").ClearContents
Range("B5:Z37").Selection.Interior.ColorIndex = xlNone

and for the dates in D4,F4,H4,J4,L4,N4 and P4

Range("D4") = Range("D4") + 7
Range("F4") = Range("F4") + 7
and so on

If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Hi Jacob,

Thanks for your help, reposted as I had no response from post yesterday.
Code clears contents but it also clears all the borders aswell, I need to
keep borders on sheet, only clear cell data and return to no fill colour.

Also I mentioned too about another range of cells that need to go forward 7
days have you any ideas how to implement this into code.

Many thanks


Terilad

"Jacob Skaria" wrote:

Adjust Range("B5:Z37") as your requirement..

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue

Range("B5:Z37").ClearContents
Range("B5:Z37").ClearComments
Range("B5:Z37").ClearFormats

End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Dear All,

I am looking for some help on a macro, I have a range of cells which require
to go forward 7 days, the code I have below works fine for the week
commencing
cell, the other cells are Days of week cells displayed as Monday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Here is the code:

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue
End Sub

I am also looking to put an extra code in so that all data in a range of
cells can be deleted and no fill colour, could anyone help me with this query.

Can anyone give me some help on this.

Kind Regards

Terilad

Oops thats my mistake. Change that to

Range("B5:Z37").Interior.ColorIndex = xlNone

If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Thanks, cells code work thankyou.

I am getting an error with the following line

Range("B5:Z37").Selection.Interior.ColorIndex = xlNone

When I remove this line from the code I get no error but the cells still
remain filled with colour, any other ideas?

Error is object doesn't support this property or method

Terilad

"Jacob Skaria" wrote:

Try the below..please remove the earlier code..

Range("B5:Z37").ClearContents
Range("B5:Z37").Selection.Interior.ColorIndex = xlNone

and for the dates in D4,F4,H4,J4,L4,N4 and P4

Range("D4") = Range("D4") + 7
Range("F4") = Range("F4") + 7
and so on

If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Hi Jacob,

Thanks for your help, reposted as I had no response from post yesterday.
Code clears contents but it also clears all the borders aswell, I need to
keep borders on sheet, only clear cell data and return to no fill colour.

Also I mentioned too about another range of cells that need to go forward 7
days have you any ideas how to implement this into code.

Many thanks


Terilad

"Jacob Skaria" wrote:

Adjust Range("B5:Z37") as your requirement..

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue

Range("B5:Z37").ClearContents
Range("B5:Z37").ClearComments
Range("B5:Z37").ClearFormats

End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Dear All,

I am looking for some help on a macro, I have a range of cells which require
to go forward 7 days, the code I have below works fine for the week
commencing
cell, the other cells are Days of week cells displayed as Monday 04 and so
on. I have moved the week comm cell data to cell N2, the other 7 cells which
require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold
days of week with date, displayed like Monday 04, Tuesday 05 and so on.

Here is the code:

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
Range("N2") = Range("N2") + 7
arrData(0) = Range("C37")
For lngRow = 5 To 37 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("C" & lngRow)
Range("C" & lngRow) = arrData(intTemp - 1)
Next
Range("C1") = varValue
End Sub

I am also looking to put an extra code in so that all data in a range of
cells can be deleted and no fill colour, could anyone help me with this query.

Can anyone give me some help on this.

Kind Regards

Terilad