VBA - Formatting Exported Data for Unpivot

[ckp90c]

Hi All - I just have a few issues on my VBA i'm creating for some macros. At the bottom I do have some of the code i've done for each part, and if they are successful yet or not.

I am using an in-house data unpivot Excel tool that requires tagging around your data and I am trying to format an OTP export automatically to get it ready for it.

I am happy to provide more context and even a dummied out sample of the file if needed. Thank you all so much!!!

---------------------------------------------------------------------------------------------------------

Part 1 - I need to add "#COL[C]" in the soonest row in column B

Part 2 - I need to add "#COL[A]" in the top row of the first column that doesn't contain header data

Part 3 - I need to add "#COL" in the cell right under "#COL[A]"

Part 4 - I need to add "#ROW" in every cell under "#COL" and will stop based on the last populated row in column B

Part 5 - I last need to add "#ROW" to the right of the populated "#COL[C]" from column B into each column on that same row, up until the last column BEFORE the column with #COL[A] and #COL, etc...technically it would stop at the second to last column.

---------------------------------------------------------------------------------------------------------


Part 1 - GOOD

Sub ColC()
Dim N As Long
N = Cells(Rows.Count, "B").End(xlUp).Row + 1
Cells(N, "B").Value = "#COL[C]"

End Sub


----------------------------------------------------------------------------------------------------------

Part 2 - GOOD

Sub ColA()


Dim N As Long
N = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(N) = "#COL[A]"

End Sub


---------------------------------------------------------------------------------------------------------
Part 3 - BAD

Sub ColB()


Dim N As Long
N = Cells(2, Columns.Count).End(xlToLeft).Column + 1
Cells(N) = "#COL"

End Sub
---------------------------------------------------------------------------------------------------------
Part 4 - Don't know how to do it
---------------------------------------------------------------------------------------------------------
Part 5 - Don't know how to do it

 

[tonyyy]

ckp90c,

Writing five separate macros seems a bit inefficient, but since that's what you requested, you might consider the following three parts...

Sub Part3()
Dim found As Range
Set found = ActiveSheet.Rows(1).Find(What:="#COL[A]", After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not found Is Nothing Then _
    found.Offset(1, 0).Value = "#COL[B]"
End Sub

Sub Part4()
Dim LastRow As Long
Dim found As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set found = ActiveSheet.Rows(2).Find(What:="#COL[B]", After:=Cells(2, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not found Is Nothing Then _
    ActiveSheet.Range(found.Offset(1, 0), Cells(LastRow, found.Column)).Value = "#ROW"
End Sub

Sub Part5()
Dim LastCol As Long
Dim found As Range
LastCol = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
Set found = ActiveSheet.Cells.Find(What:="#COL[C]", After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not found Is Nothing Then _
    ActiveSheet.Range(found.Offset(0, 1), Cells(found.Row, LastCol - 1)).Value = "#ROW"
End Sub

Cheers,

tonyyy

 

[tonyyy]

One approach to a single macro...

Sub AddStrings()
Dim LastRow As Long, LastCol As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Cells(LastRow + 1, 2).Value = "#COL[C]"
Cells(1, LastCol + 1).Value = "#COL[A]"
Cells(2, LastCol + 1).Value = "#COL[B]"
Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).Value = "#ROW"
Range(Cells(LastRow + 1, 3), Cells(LastRow + 1, LastCol)).Value = "#ROW"
End Sub