Repeat the cell value on the basis of line items available.

[Sanoj]

Hi All,

I am seriously stuck up with a macro coding. I have attached a sample file where in the Template sheet is the input which I receive.
Where From Column C and onward there is a code (here its from C2, D2, E2 & F2).
This codes should get pasted on a new output sheet from A2 under the heading GL, number of times we have text in input file A3 onwards (hence put formula in cell E1 of count if =COUNTIF(A3:A10000,"*"), and should repeat the step for C2, D2, E2 & F2 and so on if text exists.

Then in sheet out put it should also paste the respective GL values as is.

For the reference I have attached a sample sheet, I thought is would be an easy task for me but now I am like banging my head .

Somebody please help me out with this coding task.

 

[Khalid NGO]

Continued from:
http://chandoo.org/forum/threads/re...is-of-line-items-available.32323/#post-191819

 

[PCosta87]

Hi @Sanoj

Something like this perhaps:

Sub test()

    Dim c As Range
    Dim lrow, lrow1 As Integer

    lrow1 = Sheets("Template").Columns("C").Cells(Rows.Count).End(xlUp).Row
    Sheets("Output").Range("A1").Value = "GL"

    For Each c In Sheets("Template").Range("C2:F2").Cells
        lrow = Sheets("Output").Columns("A").Cells(Rows.Count).End(xlUp).Offset(1, 0).Row
        Sheets("Output").Range("A" & lrow & ":A" & lrow + Sheets("Template").Range("E1").Value - 1).Value = c.Value
        Sheets("Template").Cells(3, c.Column).Select
        Range(Selection, Selection.End(xlDown)).Copy Sheets("Output").Range("B" & lrow)
    Next c
 
End Sub

Please see attached.

Hope it helps

 

[Sanoj]

Continued from:
http://chandoo.org/forum/threads/re...is-of-line-items-available.32323/#post-191819

I posted it in wrong forum... wanted to delete it but couldn't find any option for it.
Actually I needed a VBA coding.

 

[Sanoj]

Hi @Sanoj

Something like this perhaps:

Sub test()

    Dim c As Range
    Dim lrow, lrow1 As Integer

    lrow1 = Sheets("Template").Columns("C").Cells(Rows.Count).End(xlUp).Row
    Sheets("Output").Range("A1").Value = "GL"

    For Each c In Sheets("Template").Range("C2:F2").Cells
        lrow = Sheets("Output").Columns("A").Cells(Rows.Count).End(xlUp).Offset(1, 0).Row
        Sheets("Output").Range("A" & lrow & ":A" & lrow + Sheets("Template").Range("E1").Value - 1).Value = c.Value
        Sheets("Template").Cells(3, c.Column).Select
        Range(Selection, Selection.End(xlDown)).Copy Sheets("Output").Range("B" & lrow)
    Next c

End Sub

Please see attached.

Hope it helps

Thank you PCosta87 its working.
But only thing is I wanted to paste the amount in col D instead of B.

To get a better idea please refer my sheet "Output" in file which is attached earlier.

 

[Sanoj]

Dear experts,

I need a VBA code for the attached a file which is more accurate, here working sheet represents the output how its required and Template sheet is the input how we receive.

Please help me with it.

Working file conditions requirement are:

1. Template sheet GL code (here its C2:G2 they are not fixed so I used F1 =COUNTA(C2:I2)) should be pasted n number of times in working sheet col A depending on the line item available in Template sheet A3 onwards (here i have used formula in Template!G1 =COUNTA(A3:A1000))

2. The amount of the respective GL in Template sheet should get pasted in Col D in working sheet against each GL as paste special value.

3. Text in output sheet Col G should be =IFERROR($B$1&"_"&A3&"_"&$D$1,"") form template sheet. Cell B1 of template sheet is drop down list.

4 Cost Center (is B3 to B39 but not always till B39 may be more or less) it should get pasted n number of times as cell value in F1 of template!H.

5. In working sheet if in Cost center contains text "." then it should move to next cell that is working!I its named as WBS col I.

6. And finally in output sheet if any cell in col D is zero or blank the entire row should be deleted.

* The user should not have access to the Cell of Template sheet F1 & G1 as it contains formula and accidentally they me delete or amend it which may lead to in correct output.

I request for a solution from all the Excel Experts. My working file is attached along with this message.
Please let me know in case of any question.

 

[Khalid NGO]

Hi @Sanoj

RE: Post # 6
You have started a completely different question in this thread.

If you have read the basic forum rules, you would have come across this:

• Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.

Regards,

 

[Sanoj]

Hi @Sanoj

RE: Post # 6
You have started a completely different question in this thread.

If you have read the basic forum rules, you would have come across this:

• Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.

Regards,

Hello Khalid,

No the question and requirement is same with the same file only thing is I just elaborated the explained in detail.
I don't want to duplicate the thing as the requirement is same as earlier. You will come to know if you read my first post of the thread.

Please let me know in case of any issues, and you want me to post a new Thread.

 

[Khalid NGO]

Hi Sanoj,

the question and requirement is same with the same file only thing is I just elaborated the explained in detail.

That should be done in initial post.

Please let me know in case of any issues, and you want me to post a new Thread.

You can continue here until the question is relevant to this thread.

Take care

 

[Sanoj]

Hi Sanoj,


That should be done in initial post.


You can continue here until the question is relevant to this thread.

Take care

Thanks Khalid

 

[PCosta87]

Thank you PCosta87 its working.
But only thing is I wanted to paste the amount in col D instead of B.

To get a better idea please refer my sheet "Output" in file which is attached earlier.

Hi,

Simply change

Range(Selection, Selection.End(xlDown)).Copy Sheets("Output").Range("B" & lrow)

to

Range(Selection, Selection.End(xlDown)).Copy Sheets("Output").Range("D" & lrow)

 

[PCosta87]

Dear experts,

I need a VBA code for the attached a file which is more accurate, here working sheet represents the output how its required and Template sheet is the input how we receive.

Please help me with it.

Working file conditions requirement are:

1. Template sheet GL code (here its C2:G2 they are not fixed so I used F1 =COUNTA(C2:I2)) should be pasted n number of times in working sheet col A depending on the line item available in Template sheet A3 onwards (here i have used formula in Template!G1 =COUNTA(A3:A1000))

2. The amount of the respective GL in Template sheet should get pasted in Col D in working sheet against each GL as paste special value.

3. Text in output sheet Col G should be =IFERROR($B$1&"_"&A3&"_"&$D$1,"") form template sheet. Cell B1 of template sheet is drop down list.

4 Cost Center (is B3 to B39 but not always till B39 may be more or less) it should get pasted n number of times as cell value in F1 of template!H.

5. In working sheet if in Cost center contains text "." then it should move to next cell that is working!I its named as WBS col I.

6. And finally in output sheet if any cell in col D is zero or blank the entire row should be deleted.

* The user should not have access to the Cell of Template sheet F1 & G1 as it contains formula and accidentally they me delete or amend it which may lead to in correct output.

I request for a solution from all the Excel Experts. My working file is attached along with this message.
Please let me know in case of any question.

Hi again,

Here you go (please see attached)