Home

Macro to help fill in row data between dates

%3CLINGO-SUB%20id%3D%22lingo-sub-690453%22%20slang%3D%22en-US%22%3EMacro%20to%20help%20fill%20in%20row%20data%20between%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690453%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%20I%20have%20been%20beating%20my%20head%20against%20this%20problem%20for%20some%20time%20but%20haven't%20yet%20managed%20to%20find%20a%20solution.%20I%20have%20an%20extensive%20spread%20sheet%20of%20financial%20data%20that%20i%20am%20trying%20to%20transpose%20into%20a%20different%20format.%20I%20am%20sure%20a%20VBA%20script%20will%20do%20the%20job%20but%20i%20am%20very%20inexperienced%20in%20VBA%20coding%20and%20everything%20i%20have%20tired%20so%20far%20has%20failed.%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20make%20this%20(sample%20only)%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EIndex%3C%2FTD%3E%3CTD%3EStart%20Date%3C%2FTD%3E%3CTD%3EEnd%20Date%3C%2FTD%3E%3CTD%3EMonthly%20Cost%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%201%3C%2FTD%3E%3CTD%3E01%2F01%2F2015%3C%2FTD%3E%3CTD%3E25%2F04%2F2015%3C%2FTD%3E%3CTD%3E%C2%A3100%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%202%3C%2FTD%3E%3CTD%3E02%2F04%2F2017%3C%2FTD%3E%3CTD%3E03%2F06%2F2017%3C%2FTD%3E%3CTD%3E%C2%A3120%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%203%3C%2FTD%3E%3CTD%3E03%2F05%2F2013%3C%2FTD%3E%3CTD%3E01%2F08%2F2013%3C%2FTD%3E%3CTD%3E%C2%A3150%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELook%20like%20this%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EIndex%3C%2FTD%3E%3CTD%3EStart%20Date%3C%2FTD%3E%3CTD%3EEnd%20Date%3C%2FTD%3E%3CTD%3EMonthly%20Cost%3C%2FTD%3E%3CTD%3EBilling%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%201%3C%2FTD%3E%3CTD%3E15%2F01%2F2015%3C%2FTD%3E%3CTD%3E25%2F04%2F2015%3C%2FTD%3E%3CTD%3E%C2%A350%3C%2FTD%3E%3CTD%3E15%2F01%2F2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%201%3C%2FTD%3E%3CTD%3E15%2F01%2F2015%3C%2FTD%3E%3CTD%3E25%2F04%2F2015%3C%2FTD%3E%3CTD%3E%C2%A3100%3C%2FTD%3E%3CTD%3E01%2F02%2F2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%201%3C%2FTD%3E%3CTD%3E15%2F01%2F2015%3C%2FTD%3E%3CTD%3E25%2F04%2F2015%3C%2FTD%3E%3CTD%3E%C2%A3100%3C%2FTD%3E%3CTD%3E01%2F03%2F2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%201%3C%2FTD%3E%3CTD%3E15%2F01%2F2015%3C%2FTD%3E%3CTD%3E25%2F04%2F2015%3C%2FTD%3E%3CTD%3E%C2%A333.30%3C%2FTD%3E%3CTD%3E01%2F04%2F2015%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%202%3C%2FTD%3E%3CTD%3E10%2F04%2F2017%3C%2FTD%3E%3CTD%3E10%2F06%2F2017%3C%2FTD%3E%3CTD%3E%C2%A380%3C%2FTD%3E%3CTD%3E10%2F04%2F2017%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%202%3C%2FTD%3E%3CTD%3E10%2F04%2F2017%3C%2FTD%3E%3CTD%3E10%2F06%2F2017%3C%2FTD%3E%3CTD%3E%C2%A3120%3C%2FTD%3E%3CTD%3E01%2F05%2F2017%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%202%3C%2FTD%3E%3CTD%3E10%2F04%2F2017%3C%2FTD%3E%3CTD%3E10%2F06%2F2017%3C%2FTD%3E%3CTD%3E%C2%A340%3C%2FTD%3E%3CTD%3E01%2F06%2F2017%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%203%3C%2FTD%3E%3CTD%3E05%2F05%2F2013%3C%2FTD%3E%3CTD%3E30%2F08%2F2013%3C%2FTD%3E%3CTD%3E%C2%A3125%3C%2FTD%3E%3CTD%3E05%2F05%2F2013%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%203%3C%2FTD%3E%3CTD%3E05%2F05%2F2013%3C%2FTD%3E%3CTD%3E30%2F08%2F2013%3C%2FTD%3E%3CTD%3E%C2%A3150%3C%2FTD%3E%3CTD%3E01%2F06%2F2013%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%203%3C%2FTD%3E%3CTD%3E05%2F05%2F2013%3C%2FTD%3E%3CTD%3E30%2F08%2F2013%3C%2FTD%3E%3CTD%3E%C2%A3150%3C%2FTD%3E%3CTD%3E01%2F07%2F2013%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eindex%203%3C%2FTD%3E%3CTD%3E05%2F05%2F2013%3C%2FTD%3E%3CTD%3E30%2F08%2F2013%3C%2FTD%3E%3CTD%3E%C2%A3150%3C%2FTD%3E%3CTD%3E01%2F08%2F2013%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20other%20data%20in%20other%20columns%20that%20i%20would%20also%20need%20to%20copy%20down%20but%20it%20wouldn't%20need%20to%20change%20just%20stay%20aligned%20to%20the%20correct%20index%20(in%20case%20it%20makes%20a%20difference%20i%20am%20using%20Excel%202013).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-690453%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Manf1976
New Contributor

HI, I have been beating my head against this problem for some time but haven't yet managed to find a solution. I have an extensive spread sheet of financial data that i am trying to transpose into a different format. I am sure a VBA script will do the job but i am very inexperienced in VBA coding and everything i have tired so far has failed.

I am trying to make this (sample only):

IndexStart DateEnd DateMonthly Cost
index 101/01/201525/04/2015£100
index 202/04/201703/06/2017£120
index 303/05/201301/08/2013£150

 

Look like this:

IndexStart DateEnd DateMonthly CostBilling Date
index 115/01/201525/04/2015£5015/01/2015
index 115/01/201525/04/2015£10001/02/2015
index 115/01/201525/04/2015£10001/03/2015
index 115/01/201525/04/2015£33.3001/04/2015
index 210/04/201710/06/2017£8010/04/2017
index 210/04/201710/06/2017£12001/05/2017
index 210/04/201710/06/2017£4001/06/2017
index 305/05/201330/08/2013£12505/05/2013
index 305/05/201330/08/2013£15001/06/2013
index 305/05/201330/08/2013£15001/07/2013
index 305/05/201330/08/2013£15001/08/2013

 

I have other data in other columns that i would also need to copy down but it wouldn't need to change just stay aligned to the correct index (in case it makes a difference i am using Excel 2013).

 

Any help would be much appreciated.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies