Gantt style representation in a cell based on activity relationships... « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Gantt style representation in a cell based on activity relationships...

(22 posts) (4 voices)

Started 5 months ago by scottyg
Latest reply from scottyg

Tags:

No tags yet.

scottyg

Member
Joined: Dec '12
Posts: 60

Hi All excel Legends,
This is my second post to this forum after Narayank991 effortlessly solved my first.
But I think this one is a little more curly...
I am creating SAP(database / business information system) upload sheets in excel, these are maintenance task lists and work plan data.
Part of the data includes relationships between the task lines (rows) eg. this task can't start until the last has completed or they start together...
I am trying to show a Gantt style representation in one of the cells, but the only way I can do it it to use an array formula as the ranges overlap on occasion and I need to return the last row match to get the correct answer.
This is the formula, which works perfectly, when entered and then activated manually (Ctrl, Shift and Enter);
Range("AI" & i).FormulaR1C1 = _
"=IF(RC4<>"""",IF(RC30="""",REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC[-3]=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))),IF(RC[-5]="""",REPT("" "",SUM(R[-1]C[-1],RC[-4]))&REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC32=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))))"
Range("AI" & i).Font.Name = "System"
As you can see it is in the range of 1000 chars, and there's no way I can enter it via VBA(256 limit).

This is the work book
https://www.dropbox.com/s/0702ixohl0zaes1/CMFPMDT1.xlsb
If there's some way of getting this formula down under the 256 chars limit, or some way to get the existing formula the work via VBA, I would be eternally grateful...

Posted 5 months ago #
shrivallabha

Member
Joined: Apr '12
Posts: 575

There's a similar problem discussed here:
http://www.excelforum.com/excel-programming-vba-macros/562022-application-sendkeys-shift-ctrl-enter.html
A way to overcome this limitation:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Posted 5 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

could that technique be applied to 4-5 parts? as the formula I am using is very long...
I did try this earlier and the error that I was receiving 1004 "unable to set the formula array property of the range class"
It seems that the 1st formula to be entered must be a working formula, not a broken up version of what I need, is this true?

Posted 5 months ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,363

Hi Scott ,

I have not gone through your worksheet in enough detail to rework your formula , but my first impression is that there are too many functions crammed into this one formula , which is why it is so long.

If you use even 3 to 4 helper columns , you may be able to bring down the formula length substantially ; for instance , one repetitive calculation is REPT("|",Q39) , which occurs 6 times in one cell ; repeating this over a 1000 cells is a huge load ; instead , using a helper column for this , and using that cell reference in this formula , will not only reduce the length of the formula , but may even speed up calculations , since this particular function will not need to be calculated where ever it occurs , unless the corresponding value in column Q changes.

Another reduction can be the MATCH(9999999999,... part ; even 9 appears to work ; whether it will work for all cells , I cannot say ; but you need to make it just big enough for whatever purpose it is used. Since this also occurs 6 times in each formula , the reduction will help.

Narayan

Posted 5 months ago #

shrivallabha

Member
Joined: Apr '12
Posts: 575

I think so.

Here's a makeshift crude route which seems to work.

Sub MaybeThis()
'Base formulas in 39th row
Range("AH39").FormulaR1C1 = "=LEN(RC[1])"
Range("AI39").FormulaR1C1 = "=IF(RC4<>"""",IF(RC30="""",REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC[-3]=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))),IF(RC[-5]="""",REPT("" "",SUM(R[-1]C[-1],RC[-4]))&REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC32=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))))"
Range("AI39").Select

'Replicate CSE manual behavior
Application.SendKeys "{F2}", True
Application.SendKeys "^(+~)", True

'Wait for two seconds for calculational delays
Application.Wait (Now() + TimeValue("00:00:02"))

'Fill Down formula
Range("AH39:AI100").FillDown
End Sub

Posted 5 months ago #

NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,363

Hi Scott ,

I am not very sure but can you verify the formulae in column AG ?

I may not have understood it correctly , but is it dependent on the values in column AB and column AF ? Each cell starts off from the point where the earlier cell ( according to the value in column AB ) has left off , and the number of bars corresponds to the difference in the values in column AF ; for example , suppose we consider rows 92 to 100 - rows 93 through 96 have a value of 70 in column AB ; hence the bars in these rows will start from where the bars corresponding to 60 in column AB ended ; the values in column AF are all 59 , and all the bars will be of the same length.

The bar in row 100 also has a value of 80 in column AB , which means it will start from the same point as the above bars ; since AF100 is 63 , there will be 4 bars in AG100.

If we now extend this reasoning to row 117 , AB117 has a value of 110 , which is after the value of 80 in AB112 ; the value in AF117 is 73 , which is 2 more than the value in AF112 ( 71 ) ; according to the above reasoning , the cell AG117 should have had 2 bars , which would have started after the bars in AG112 ended ; however AG117 has 4 bars , which means it is taking 70 as the from point instead of 80 ( AB111 has a value of 70 , and AF111 has a value of 69 ).

Narayan

Posted 5 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

Hi Narayan,
Thank you for taking the time to analyze this sheet for me, I am a vba newby and need all the help I can get...
I will try to make clear how this formula represents a gantt of the tasks in this sheet.
Firstly to clear any confusion about which column we are talking about, please remember the code adds 2 columns to the left side of the sheet, you may have been looking at the sheet without these 2 columns added, which explains why your comments are 2 columns out.
My description below is after the code has run and added the columns...
The sheet is made up of a number of task lists (named in Col 4).
A task list is a list of tasks that need to be done in order to complete a job or mission.
If we take task list 5 ($TL5 in Col 4) rows 101 to 117, this task list is made up of 17 operation lines (tasks), they are each "named" in order, in column 14.

For each operation line (tasks) we have several elements that are used to make up the gantt representation, Name of the operation line(col 14), Duration of the operation(col 19), then "Relationships" made up of; Operation name(col 30), Offset(col 31) this adds to the start time and Relationship Type(col 32). Then we have in col 34 a total number of hours.
So the Gantt representation looks firstly to see if there is anything in col 4, if there is it knows that it is the first line in the task list and just REPT "|" by the number of hours in col 19.
If there is nothing in col 4 it then looks at the "Relationship Type", if it is "SS" (Start-Start(they start together)), this is achieved by looking at the relationship name in the index formula and returning the number of total hours(col 34) minus the duration hours(col 19) plus any offset(col 31).
If there is nothing in col 4 and the relationship type is "FS" (Finish-Start(when the first one finishes the next starts), it looks for the operation name in the index formula to return the total hours(col 34) of that operation to REPT " " (spaces) then adds any offset(col 31) REPT " ", then adds the duration from col 19 REPT "|".
If there is nothing in col 4 and nothing in col 32 (Relationship type), then the formula defaults to a "FS" style same as above.

Wow that was long winded and I apologize if I am telling you things you already know, but, by typing this out I have realized I can shorten this formula using some of what you mentioned in your earlier reply and by combining some of the above with AND(). I'm not sure if I'll get it down under 255 chars though, if not I'll give the sendkeys method a go mentioned by Shrivallabha...

Posted 5 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

Hi Shrivallabha,
Thank you for taking the time to look at this problem.
I tried your suggestion above, but was unable to get it activate the array formula.
This is the code in the sheet.

' Range("AH39").FormulaR1C1 = "=LEN(RC[1])"
Range("AI39").FormulaR1C1 = _
"=IF(RC4<>"""",REPT(""|"",RC[-16]),IF(RC[-5]="""",REPT("" "",SUM(R[-1]C[-1],RC[-4]))&REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC32=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))))"
Range("AI39").Font.Name = "System"
'Next i
Range("AI39").Select
Application.SendKeys "{F2}", True
'Application.Wait (Now() + TimeValue("00:00:02"))
Application.SendKeys "^(+~)", True
Application.Wait (Now() + TimeValue("00:00:02"))
Range("AH39:AI" & LastRow).FillDown'

I even tried the wait between sendkeys...
I was able to reduce the chars of the formula somewhat but still well over the 255 chars...

Posted 5 months ago #

scottyg

Member
Joined: Dec '12
Posts: 60

ok,
I tried the replace method but getting the error 13 "type mismatch"
The first part frmpart(1) goes in the cell and returns a result as an array, but then the replace bit gets the error...

Sub test()
Dim i As Long
Dim frmpart(1 To 2) As String

Dim X_X_X() As String

frmpart(1) = "=IF(RC4<>"""",REPT(""|"",RC[-16]),IF(RC[-5]="""",REPT("" "",SUM(R[-1]C[-1],RC[-4]))&REPT(""|"",RC[-16]),""X_X_X()""))"

frmpart(2) = "IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC32=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))"

        '. Insert a Gantt representation
        For i = 39 To 100
        Range("AH" & i).FormulaR1C1 = "=LEN(RC[1])"
        With Range("AI" & i)
            .FormulaArray = frmpart(1)
            End With
            Next i
            For i = 39 To 100
            With Range("AI" & i)
            .Replace "X_X_X()", frmpart(2) '<<getting error 13 here
            .Font.Name = "System"
            End With
        Next i
        MsgBox "Complete"
End Sub

I am not sure why this would happening??

This is the array part of the formula,
INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)

Is there another way to return the second match(if more than one match) to get the correct row result? Without using an array formula?

Posted 5 months ago #

shrivallabha

Member
Joined: Apr '12
Posts: 575

Hi Scottyg,

Your sample sheet is very huge for sample.

The flaw with the code I had posted was asynchronous behavior of sendkeys with the rest of the code as you also experienced.

Caution: Run the code ONLY when the sheet concerned (TL Request) is your active window otherwise sendkeys will give you strange / unexpected results.

Copy the code below and use it as it is:

Sub MaybeThis2()
Dim i As Long
With Application
    .Calculation = xlCalculationManual
    For i = 39 To 1970
        Range("AH" & i).FormulaR1C1 = "=LEN(RC[1])"
        Range("AI" & i).FormulaR1C1 = "=IF(RC4<>"""",IF(RC30="""",REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC[-3]=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))),IF(RC[-5]="""",REPT("" "",SUM(R[-1]C[-1],RC[-4]))&REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC32=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))))"
        Range("AI" & i).Select

        'Replicate CSE manual behavior
        .SendKeys "{F2}", True
        .SendKeys "^(+~)", True

        'To handle asynchronous handling we put in doevents
        DoEvents
    Next i
    .Calculation = xlCalculationAutomatic
    End With
End Sub

Posted 5 months ago #

NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,363

Hi Scott ,

I have not understood everything that you have explained , but it would be helpful if we examined your formula in any one cell , say AG39 :

=IF($D39<>"",
IF($AD39="",REPT("|",Q39),
IF(AD39="SS",REPT(" ",INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),21)-INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),6)+AC39)&REPT("|",Q39),
IF(AD39="FS",REPT(" ",INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),21)+AC39)&REPT("|",Q39),""))),IF(AB39="",REPT(" ",SUM(AF38,AC39))&REPT("|",Q39),
IF(AD39="SS",REPT(" ",INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),21)-INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),6)+AC39)&REPT("|",Q39),
IF($AD39="FS",REPT(" ",INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),21)+AC39)&REPT("|",Q39),""))))

I have split up the formula on several lines so that the patterns are clear ; there are repetitions of three formulae :

1. REPT("|",Q39)

2. REPT(" ",INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),21)-INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),6)+AC39)

3. REPT(" ",INDEX(L30:AF40,MATCH(9999999999,IF(L30:L40=AB39,1,NA())),21)+AC39)

If you use a helper column for each of the above , the length of the formula will be reduced substantially. From an initial length of over 600 , the length of the revised formula came to 175.

If you are interested , I can upload the revised workbook.

Narayan

Posted 5 months ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,363

Hi Scott ,

I would like to specify the following logic to derive the contents of the cells in column AG :
`
1. Either we are on the first line , where the cell in column D has the description of the task , or we are on the subsequent lines where the cell in column AD has SS.

In both the above cases , the bars will start from the beginning of the cell ; the formula will be a simple REPT("|",Q39).

2. In all other cases , the bars will start from the end of the preceding bars , and the number of bars will still be given by a simple REPT("|",Q39).

According to me , the entire complicated formula can be replaced by just two nested IF statements , specifying the above logic. Using helper columns , the length of the formula will be less than 50 characters ; even without the helper columns , the length of the formula should be less than 255 characters.

Narayan

Posted 5 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

HI Narayan,
I have modified it somewhat and shortened it quite a bit, but as to your first point,

"In both the above cases , the bars will start from the beginning of the cell ; the formula will be a simple REPT("|",Q39)."

Not quite correct as the SS indicates a start start with the related line, which may or may not be at the beginning of the cell, so it must replicate the starting point of the related line.

Other than that you are correct, not forgetting about the offset though (col AE) this must be added in all cases except where there is no relationship.

This is the current formula
(1)IF($D39<>"",
REPT("|",S39),
the above is basic

(2)IF(AD39="",
REPT(" ",SUM(AH38,AE39))&REPT("|",S39),
the above means that; if there is no relationship it must default to a FS with the line above it. This is a SAP default...

(3)IF(AF39="SS",
REPT(" ",INDEX(N30:AH40,MATCH(9,IF(N30:N40=AD39,1,NA())),21)-INDEX(N30:AH40,MATCH(9,IF(N30:N40=AD39,1,NA())),6)+AE39)&REPT("|",S39),
the above calculates the starting position of a SS relationship, which as stated earlier, may or may not be the start of the cell.

(4)IF($AF39="FS",
REPT(" ",INDEX(N30:AH40,MATCH(9,IF(N30:N40=AD39,1,NA())),21)+AE39)&REPT("|",S39),""))))
the above formula finds the total hours of the related line as a starting position.

I really appreciate your help on this and I have had a bit of a think as to other helper columns, but I am already using 6 other columns to make this representation.

If you have time can you post and example of how you would do it, please?

I think if possible I need to avoid the array formula;
INDEX(N30:AH40,MATCH(9,IF(N30:N40=AD39,1,NA())),21)
But this is easier said than achieved...

Posted 5 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

HI shrivallabha,
Your code works perfectly, when executed from the macros list, but I can't seem to incorporate it into my existing macro, below is something I have tried.
The sub Gantt being exactly as is called from the macro list...

With Sheets("TL request")
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

.Activate
Call Gantt

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
End With

Posted 5 months ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,363

Hi Scott ,

I am still not sure that we are talking about the same thing ! I see references to column AH in your latest post , which I have not seen in your uploaded workbook.

Anyway , I used your uploaded file , and reworked the formula in column AG ; if this is not what you wanted , ignore my post.

http://speedy.sh/NF8a3/CMFPMDT1-Revised.xlsb

I have used 3 helper columns , BA , BB and BC.

I have added a new sheet , where I compared the result in column AG using the formula in your uploaded file , and the revised formula ; the formulae in column C compares the two results ; they are identical in all cases.

Narayan

Posted 5 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

Hi Narayan,
I see the confusion, we are taking about the same thing, but on different sheets, I am talking about "TL request" sheet, and think you are in "TL data", my fault sorry.
Anyway, you have solved the problem, you split the formula up into separate columns, which is a win...
I will use what you have done, thank you very much Narayan...

Shrivallabha if you could help me to insert the sendkeys code into the original macro I would be stoked, just to learn something...

Posted 5 months ago #

shrivallabha

Member
Joined: Apr '12
Posts: 575

Hi Scottyg,

Remove your application settings (Screenupdating, Enableevents, calculation) and then test them one by one.

Or try calling the sub below (as it is):

Sub MaybeThis3()
Dim i As Long
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = True
    Sheets("TL request").Activate
    Range("AH39").FormulaR1C1 = "=LEN(RC[1])"
    Range("AI39").FormulaR1C1 = "=IF(RC4<>"""",IF(RC30="""",REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC[-3]=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))),IF(RC[-5]="""",REPT("" "",SUM(R[-1]C[-1],RC[-4]))&REPT(""|"",RC[-16]),IF(RC[-3]=""SS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)-INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),6)+RC[-4])&REPT(""|"",RC[-16]),IF(RC32=""FS"",REPT("" "",INDEX(R[-9]C[-21]:R[1]C[-1],MATCH(9999999999,IF(R[-9]C[-21]:R[1]C[-21]=RC[-5],1,NA())),21)+RC[-4])&REPT(""|"",RC[-16]),""""))))"
    Range("AI39").Select

    'Replicate CSE manual behavior
    .SendKeys "{F2}", True
    .SendKeys "^(+~)", True

    'To handle asynchronous handling we put in doevents
    DoEvents
    .ScreenUpdating = False

    Range("AH39:AI1970").FillDown

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

Posted 5 months ago #

scottyg

Member
Joined: Dec '12
Posts: 60

Hi shrivallabha,
Sorry for taking so long to get back to this.
I gave this another go, but alas, was unable to get it to work.

ScottyG

Posted 4 months ago #
SirJB7

Excel Ninja
Joined: Dec '11
Posts: 4,919

Hi, socttyg!
I just tried to download your uploaded file so as to implement shrivallabha's solution and help you to finally solve your issue but there's nothing at the posted link.
Regards!

Posted 4 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

Hi SirJB7
I have reattached the book here, thanks for taking a look.
This is a biggish file with lots of stuff going on, it's sort of all interlinked but this Gantt issue relates to sheet "TL request" and run from the TLdataImport form...

https://www.dropbox.com/s/u7yrs63ro7zkb74/CMFPMDT1%20%282%29_2.xlsb

ScottyG

Posted 4 months ago #
SirJB7

Excel Ninja
Joined: Dec '11
Posts: 4,919

Hi, scottyg!
It's a really complex workbook that exceeds what I thought it was the insertion of shrivallabha code replacing the existing one, but I'm afraid I am not able to analyze the whole workbook to understand how it works and where to put my hands on, so I apologize but you'd better wait for those who have gone thru this learning process yet.
Regards!

Posted 4 months ago #
scottyg

Member
Joined: Dec '12
Posts: 60

Thank you for your time SirJB7
It is appreciated...

Posted 4 months ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics

» Excel for Beginners - Tutorials
» Excel Formula Examples
» Array Formulas
» Excel Charts
» Pivot Tables
» Project Management using Excel
» Excel Dashboards
» Excel VBA
» Advanced Excel

Our Products

Full Training Programs
» Excel School
» Excel Dashboard Classes
» Excel VBA Classes
» Excel Financial Modeling
» Excel for PMs
Crash Courses
» Excel Formula Course
» Excel Dashboards Course
Downloads
» Excel PM Templates
» Excel Formula E-book

About us

» About Chandoo.org
» Welcome
» Best of Chandoo.org
» FREE Excel Downloads
» Contact Details
» Excel Forums

Username	Password
Remember me