Excel VBA将可变列范围转置为可变行 [英] Excel VBA Transpose Variable Column Range to Variable Rows

问题描述

Hello StackOverFlow社区，

Hello StackOverFlow Community,

不久前，我开始使用excel vba，可以真正使用一些复杂问题的帮助.

I started working with excel vba not too long ago and could really use some help with a somewhat complex problem.

我有一个电子表格，其中有一列主要"部分及其下方的替代"部分.我需要创建一个宏，它将宏将Variable Alternative部件转置到与其关联的Prime部件的右侧.因此，对于下面的示例，在A列中，"P"是主要零件，"A"是Altenates:

I have a spreadsheet with a column of "Prime" parts and its "Alternative" Parts below it. I need to create a macro that will transpose the Variable Alternative parts to the right of its associated Prime part. So for the Example below, in Column A "P" are Prime Parts and "A" are Altenates :

1P |

1A |

1A |

1A |

2P |

2A |

2A |

3P |

3A |

我试图创建一个宏，该宏将给我以下结果:

I trying to create a macro that will give me the following results:

1P | 1A | 1A | 1A

1P | 1A | 1A | 1A

1A |

1A |

1A |

2P | 2A | 2A

2P | 2A | 2A

2A |

2A |

3P | 3A

3A |

下面是我能想到的代码，但是所有其他部分都合并为一个范围，并换位到列表的第一个Prime部分.我了解这可能不是我要完成的最佳方法.我愿意接受所有建议，并期待听到一些很棒的解决方案.

Below is the Code that I was able to come up with, but all of the Alternate parts consolidate into one range and transpose to the first Prime part of the list. I understand that this may not be the best method for what I am trying to accomplish. I am open to all suggestion and looking forward to hearing some awesome solutions.

请注意，以上示例中的粗体素部分实际上已在我的电子表格中突出显示，这将解释代码中的"colorindex = 6"

Please note that the Bolded Prime parts in the above example are actually highlighted on my spreadsheet which would explain the "colorindex = 6" in the code

Sub NewHope()

Dim cell As Range
Dim LastRow As Long
Dim Prime As Range
Dim alt As Range


LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & LastRow)
    If cell.Interior.ColorIndex = 6 Then
        If Prime Is Nothing Then
            Set Prime = cell
        End If
    Else
        If alt Is Nothing Then
            Set alt = cell
        Else
            Set alt = Union(alt, cell)
        End If

    End If
Next

alt.Copy
Prime.Offset(0, 4).PasteSpecial Transpose:=True

End sub

推荐答案

尝试以下代码:

Sub test()
Dim cell As Range
Dim LastRow As Long
Dim PrimeRow As Long
Dim PrimeColumn As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & LastRow)
    If cell.Interior.ColorIndex = 6 Then
        PrimeRow = cell.Row
        PrimeColumn = cell.Column + 1
    Else
        Cells(PrimeRow, PrimeColumn).Value = cell.Value
        PrimeColumn = PrimeColumn + 1
    End If
Next

End Sub

这篇关于Excel VBA将可变列范围转置为可变行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！