如何为Excel工作表编写宏VBA代码以生成报告。 [英] How to write macro VBA code for excel sheet to generate a report.

问题描述

实际上我有这样的excel表。

Actually i have an excel sheet something like this.

Feature 	Epic	User Story	Task/Defect
F1			
	        EP1		
		             US11	
			                    T11
			                    D11
			                    D12
		             US12	
			                    T21
			                    T22
	        EP2		
		             US21	
			                    T21
			                    D21
			                    D22
		             US22	
			                    T3
			
F2                    			
	        EP4		
		              US1	
			                    T4

但是我需要编写VBA代码，它会给出类似下面的输出

But i need to write VBA code which will give output something like below

Feature 	Epic	User Story	Task/Defect
F1	         EP1	    US11	       T11
F1	         EP1	    US11	       D11
F1	         EP1	    US11	       D12
F1	         EP1	    US12	       T21
F1	         EP1	    US12	       T22
F1	         EP2	    US21	       T21
F1	         EP2	    US21	       D21
F1	         EP2	    US21	       D22
F1	         EP2	    US22	       T3
F2	         EP4	    US1	           T4

请帮帮我。在此先感谢。



我的尝试：



我会喜欢将树视图结构更改为上面描述的普通表格格式。

Please help me. Thanks in advance.

What I have tried:

I would like to change tree view structure to normal tabular format as decribed above.

推荐答案

假设数据存储在AD列中并从第2行开始（forst row = headers） ，检查一下：

Assuming that data are stored in columns A-D and starts from row 2 (forst row = headers), check this:

Option Explicit


Sub ExplodedDataToTable()
    Dim srcWsh As Worksheet
    Dim i As Long, r As Long

On Error GoTo Err_ExplodedDataToTable

    Set srcWsh = ThisWorkbook.Worksheets(1) 'you can pass the name of worksheet
    i = 2
    r = srcWsh.Range("D" & srcWsh.Rows.Count).End(xlUp).Row
    
    Do While i < r
        'remove empty row
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("A" & i).EntireRow.Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A is not empty
        If srcWsh.Range("A" & i) <> "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("B" & i & ":D" & i).Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A & B is not empty
        If srcWsh.Range("A" & i) <> "" And srcWsh.Range("B" & i) <> "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("C" & i & ":D" & i).Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A, B & C is not empty
        If srcWsh.Range("A" & i) <> "" And srcWsh.Range("B" & i) <> "" And _
            srcWsh.Range("C" & i) <> "" And srcWsh.Range("D" & i) = "" Then
            srcWsh.Range("D" & i).Delete xlShiftUp
            r = r - 1
            i = i - 1
            GoTo SkipNext
        End If
        'A, B & C is empty, D is not empty
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) = "" And srcWsh.Range("D" & i) <> "" Then
            srcWsh.Range("A" & i - 1 & ":C" & i - 1).Copy srcWsh.Range("A" & i)
            GoTo SkipNext
        End If
        'A, B is empty, C & D is not empty
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) = "" And _
            srcWsh.Range("C" & i) <> "" And srcWsh.Range("D" & i) <> "" Then
            srcWsh.Range("A" & i - 1 & ":B" & i - 1).Copy srcWsh.Range("A" & i)
            GoTo SkipNext
        End If
        'A, is empty, B, C & D is not empty
        If srcWsh.Range("A" & i) = "" And srcWsh.Range("B" & i) <> "" And _
            srcWsh.Range("C" & i) <> "" And srcWsh.Range("D" & i) <> "" Then
            srcWsh.Range("A" & i - 1).Copy srcWsh.Range("A" & i)
            GoTo SkipNext
        End If
        
SkipNext:
        i = i + 1
    Loop
    

Exit_ExplodedDataToTable:
    On Error Resume Next
    Set srcWsh = Nothing
    Exit Sub

Err_ExplodedDataToTable:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_ExplodedDataToTable

End Sub

它不是那样的工作。

我们不为你工作。

如果你想要有人写你的代码，你必须支付 - 我建议你去Freelancer.com并在那里问。



但要注意：你得到的是你付出的代价。支付花生，买猴子。



发展的概念就像这句话所暗示的那样：系统地运用科学和技术知识来满足特定的目标或要求。 BusinessDictionary.com [ ^ ]

这与有一个不一样快速谷歌并放弃，如果我找不到完全正确的代码。

所以要么付钱给别人去做，要么学会如何自己写。我们不是为你做这件事。

It doesn't quite work like that.
We do not do your work for you.
If you want someone to write your code, you have to pay - I suggest you go to Freelancer.com and ask there.

But be aware: you get what you pay for. Pay peanuts, get monkeys.

The idea of "development" is as the word suggests: "The systematic use of scientific and technical knowledge to meet specific objectives or requirements." BusinessDictionary.com[^]
That's not the same thing as "have a quick google and give up if I can't find exactly the right code".
So either pay someone to do it, or learn how to write it yourself. We aren't here to do it for you.

这篇关于如何为Excel工作表编写宏VBA代码以生成报告。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！