VBA Code for Shifting Data in Cells based on "Start Month"

pinkcookie

New Member
Joined
Mar 23, 2016
Messages
4
I'm trying to write a VBA code that shifts the data of a cell based on the start month, so for example:

  • if the start month is March then the "data" set starts in March (and there's a 12 month table, starting from Jan. to Dec., each month is a column, the start month will be indicated as a text/string before the 12 columns, on the same row)

I want to make it such that if I change the start month to, let's say June, then the data will shift 3 cells to the right.

I've tried using offset and match functions on excel and I tried coding it in vba but I'm having no luck.

Sorry if the formating for this question is weird, it's my first time posting for excel help!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Steve_R

Active Member
Joined
Oct 28, 2015
Messages
350
Hi Pincookie, welcome to the forum.
It is hard to understand precisely what you want without seeing a sample but I suspect you may be after something like the following?

Excel 2010
ABCDEFGHIJKLMN
1DateDataJanFebMarAprMayJunJulAugSepOctNovDec
215/01/2017VPGVPG
316/06/2016LESLES
429/07/2016PUKPUK
517/03/2017EHKEHK
69/06/2016HCBHCB
713/11/2016BLIBLI
819/09/2016IXDIXD
97/10/2016LWYLWY
1019/02/2017TYXTYX
1127/08/2016SQFSQF
1214/08/2016IRNIRN
135/09/2016KPSKPS
146/05/2016FVQFVQ
156/05/2016UUAUUA
162/10/2016QYRQYR
1715/12/2016ISZISZ
1818/09/2016CQPCQP
1927/03/2016IBHIBH
2029/11/2016NIKNIK
211/10/2016KPNKPN

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C2=IF(TEXT($A2,"mmm")=C$1,$B2,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Drag or copy the formula to other cells.

If that doesn't do what you want, post a small sample showing what you have and how it should look after. At the bottom of my signature is a link where you will find instructions for posting aids (I used Mr Excel HTML Maker above).
 

pinkcookie

New Member
Joined
Mar 23, 2016
Messages
4
Hi Pincookie, welcome to the forum.
It is hard to understand precisely what you want without seeing a sample but I suspect you may be after something like the following?

Original data (the numbers are the "data")
ABCDEFGHIJKLMN
1Start MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Mar15151515
3Jan101010121212
4Sep656733

<tbody>
</tbody>

New version - after changing the start months (for the first one only)
ABCDEFGHIJKLMN
1Start MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Jul15151515
3Jan101010121212
4Sep656733

<tbody>
</tbody>

Drag or copy the formula to other cells.

If that doesn't do what you want, post a small sample showing what you have and how it should look after. At the bottom of my signature is a link where you will find instructions for posting aids (I used Mr Excel HTML Maker above).


Hi! Thanks for replying, sorry I didn't know how to get a sample of my sheet up here $:

But I've modified what you posted to look more like what I'm looking for...

From the original to the new version, I've changed the "Start Month" from Mar to Jul, and so the data that was originally from Mar - Jun shifts to Jul - Oct...

Was this a bit more clear in what I was looking for?

Thanks in advance for looking into this!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

sorry I didn't know how to get a sample of my sheet up here
Steve already pointed you to that information. ;)

At the bottom of my signature is a link where you will find instructions for posting aids (I used Mr Excel HTML Maker above).
(There's one in my signature block below as well. :))
 

Steve_R

Active Member
Joined
Oct 28, 2015
Messages
350

ADVERTISEMENT

Hi! Thanks for replying, sorry I didn't know how to get a sample of my sheet up here $:

But I've modified what you posted to look more like what I'm looking for...

From the original to the new version, I've changed the "Start Month" from Mar to Jul, and so the data that was originally from Mar - Jun shifts to Jul - Oct...

Was this a bit more clear in what I was looking for?

Thanks in advance for looking into this!

The hard part at this end is understanding what you have, your 'rules' and what your plan is. The more detail you are able to provide, the easier it is for people to help you. A HTML posting aid helps us see the exact layout you have in your sheet. For example, we can see dates and what column all data are in, which would reduce the chance of writing code that works with incorrect formats or columns. One thinkg we don't want to see is sensitive data so change anything sensistive.

Interpretting what you posted above, I have some questions:
1) If you change the month in column A (for example) from Mar to Aug, copy everything that currently exists in Mar, April, May, etc and make the new start column I (Aug).
2) What if data in you table would extend beyond Dec when moved?
3) Are there likely to be any data left of the month (e.g. you have Apr in column A but have data in B,C,D)?
4) Are any data below the table or is everything blank (thinking we may need to use a worksheet change event, intercepting change in column A to automate the process)?
 

pinkcookie

New Member
Joined
Mar 23, 2016
Messages
4
The hard part at this end is understanding what you have, your 'rules' and what your plan is. The more detail you are able to provide, the easier it is for people to help you. A HTML posting aid helps us see the exact layout you have in your sheet. For example, we can see dates and what column all data are in, which would reduce the chance of writing code that works with incorrect formats or columns. One thinkg we don't want to see is sensitive data so change anything sensistive.

Interpretting what you posted above, I have some questions:
1) If you change the month in column A (for example) from Mar to Aug, copy everything that currently exists in Mar, April, May, etc and make the new start column I (Aug).
2) What if data in you table would extend beyond Dec when moved?
3) Are there likely to be any data left of the month (e.g. you have Apr in column A but have data in B,C,D)?
4) Are any data below the table or is everything blank (thinking we may need to use a worksheet change event, intercepting change in column A to automate the process)?

Original data (the numbers are the "data")
ABCDEFGHIJKLMN
1ItemStart MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Task OneMar15151515
3Task TwoJan101010121212
4Task ThreeSep656733

<tbody>
</tbody>


After switching the value in B2 from Mar to Aug
ABCDEFGHIJKLMN
1ItemStart MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Task OneMar15151515
3Task TwoJan1010101212121313
4Task ThreeSep656733

<tbody>
</tbody>


After switching the value in B3 from Jan to Sep
ABCDEFGHIJKLMN
1ItemStart MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Task OneMar15151515
3Task TwoSep1010101212121313
4Task ThreeSep656733


<tbody>
</tbody>

2) It's alright if the data extends out after Dec as there would be nothing to the right of the sheet. But if it's possible to make the max stop at Dec that would be good too (but that would mean removing the content after the Dec column, but I'd want to retain any of the content cleared somehow,so that if I called an earlier month after, I'd still be able to produce the data again - as shown above)

3) There will never be any data left of the starting month.

4) The worksheet will be blank aside from this table.

Thanks!</div>
 

pinkcookie

New Member
Joined
Mar 23, 2016
Messages
4
Original data (the numbers are the "data")
ABCDEFGHIJKLMN
1ItemStart MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Task OneMar15151515
3Task TwoJan101010121212
4Task ThreeSep656733

<tbody>
</tbody>


After switching the value in B2 from Mar to Aug
ABCDEFGHIJKLMN
1ItemStart MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Task OneMar15151515
3Task TwoJan1010101212121313
4Task ThreeSep656733

<tbody>
</tbody>


After switching the value in B3 from Jan to Sep
ABCDEFGHIJKLMN
1ItemStart MonthJanFebMarAprMayJunJulAugSepOctNovDec
2Task OneMar15151515
3Task TwoSep1010101212121313
4Task ThreeSep656733

<tbody>
</tbody>

2) It's alright if the data extends out after Dec as there would be nothing to the right of the sheet. But if it's possible to make the max stop at Dec that would be good too (but that would mean removing the content after the Dec column, but I'd want to retain any of the content cleared somehow,so that if I called an earlier month after, I'd still be able to produce the data again - as shown above)

3) There will never be any data left of the starting month.

4) The worksheet will be blank aside from this table.

Thanks!

I forgot to answer the first question, that's correct, when Mar changes to Aug, all the values from Mar, Apr, May, etc. is copied to Aug, Sep, Oct, etc
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
2) It's alright if the data extends out after Dec as there would be nothing to the right of the sheet. But if it's possible to make the max stop at Dec that would be good too (but that would mean removing the content after the Dec column, but I'd want to retain any of the content cleared somehow,so that if I called an earlier month after, I'd still be able to produce the data again - as shown above)
There's a bit of circling around there, but if you are happy to have the excess hang out to the right so it can be retrieved, try this in a copy of your workbook.

This would go in the worksheet's code module.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rData As Range
  Dim newCol As Long
  
  If Target.Count > 1 Then Exit Sub
  If Target.Column = 2 And Target.Row > 1 Then
    On Error Resume Next
    Set rData = Target.Offset(, 1).Resize(, 12).SpecialCells(xlConstants).Cells(1)
    newCol = Range("C1:N1").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    If Not rData Is Nothing And newCol > 0 Then
      Application.EnableEvents = False
      rData.Resize(, 23).Cut Destination:=Target.Offset(, newCol - Target.Column)
      Application.EnableEvents = True
    End If
  End If
End Sub

If you don't want to see any of the data hanging out to the right of the table, just hide columns O:Y
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top