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.