Posts by MrRedli

    Re: vba to locate first cell of empty column


    try below code:


    Hi guys, I have hit a dead end and can’t think further… what I am tiring to do is a simple incremental count on column W.


    The problem what I have is I need the length of the cell to remain at 10 character only, and I cannot use cell format for this as it is not working when I import this into system.


    Cell reference has to be IN00000001, IN00000002…..IN00000010, IN00000011 …IN00000100 etc…


    The formula will be based on cell W2 where W2 is manually entered in. then I want a formula which is get the next incremental value in cell W3, w4 and so on… but in set format

    Re: IF formula evaluates to wrong value


    does " Sell_Investment" refer to one cell or range? if range then will not get the address as you using range when you answer of each cell. try using if(A1="YES",1,2) assuming column a is where you have the range "Sell_Investment".

    Re: USD 25$ - help to combine mutliple files in set format


    hi KrishnaKumar,


    Thanks for the update however the Vlookup formula is converted to value only instead of the formula, so I have commented them out, however when I open the master file it come up with links to update and when I check the Vlookup formula is referring the macro file instead of the master combined. Can you please check this?

    Re: USD 25$ - help to combine mutliple files in set format


    Hi KrishnaKumar,


    some very small change as below:



    • can all the number for each division from column E to AU in all sheet except summary to turned into number format with 2 decimal place only.
    • Can the grand total after each sector to color the cells as light grey --> .ThemeColor = xlThemeColorDark1 .TintAndShade = -5
    • Sheet to be set with print range as landscape 1 page wide and 2 page tall; with row 8 to be heading row for each page
    • One last one can it save the file without the template tab only into the directory was select for the macro, naming as "Master Combined.xlsx"

    Hi guys,
    Hope someone can help me get this macro in place?
    I have multiple workbooks for each sector and all file names will start with sector ID such as RAD, SON, SEC etc (about 7 files) and each of these 7 files will have multiple sheet(can have put to 60 tab) (Attached book as sample RAD and SON). I want the macro to go through the list of to open the file (based on list in key tab in "Final output" file in cell J2 to J5) and combine each tab based on the division name which is always in cell A7.
    However here is the issue each division can have multiple sheet in the same file and column A to K will be constant across each of these tab for the same division. (example RAD ScheduleReport file Sheet 1 to 4 is for division BON and A8 to K25 is same across these 4 sheets). But the column after K will be different which I need to merge into my Final output file based on the column heading which is pre-set. Also if you notice the workbook has got names in merged cell with last name, first name which i would like slipt when copied into my master sheet.
    Once it completes the file for say RAD, it should start again with SON in the same format but it will paste below the data which was combined while running through RAD) and should continue until the list runs so basically from J2 to J5)
    After each sector is pasted completely I need total for each sector in column F, H and I in final output file. And then a grand total of each division at the bottom on the file to show total of each divisions.
    This grand total for the sector should then be copied for column F, H and I into my summary sheet in column B,C &D to give an over view for each division.
    This file is exported daily so the number of rows will not be constant hence the macro should start as fresh file always, and also be able to give option to select the folder to get files from.



    10% has been paid.