Home Loop headers and give chosen, distinct names to duplicate values
Reply: 1

Loop headers and give chosen, distinct names to duplicate values

Gurrito
1#
Gurrito Published in 2018-01-12 15:47:09Z

I have an export sheet that reuses the same column name multiple times. The export is structured in a way that I know which duplicate value applies to which level, but I'm having a hard time looping through the range and updating the values. What's happening is that the code below either sets every single header to the first Case scenario, or nothing at all.

Here's what I have so far:

Dim i As Integer
Dim lastCol As Long
Dim Cell As Range
Dim lr As String
Dim lrb As Integer

lr = "1st level Close Record"
lrb = 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print lastCol

For Each Cell In Range(Cells(1, 1), Cells(1, lastCol))
    Select Case Cell.Value = "Close Record"
        Case lrb = 1
            Debug.Print Cell.Value
            Cell.Value = lr
            lr = "2nd Level Close Record"
            lrb = 2
        Case lrb = 2
            Cell.Value = lr
            lr = "3rd Level Close Record"
            lrb = 3
        Case lrb = 3
            Cells.Value = lr
    End Select
Next
Scott Holtzman
2#
Scott Holtzman Reply to 2018-01-12 16:03:45Z

Code below tested and it works. Pay attention to the logic difference in below versus yours.

Option Explicit

Sub Test()

Dim i As Integer
Dim lastCol As Long
Dim Cell As Range
Dim lr As String
Dim lrb As Integer

lr = "1st level"
lrb = 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print lastCol

For Each Cell In Range(Cells(1, 1), Cells(1, lastCol))
    Select Case Cell.Value = "Close Record"
        Case True
            Select Case lrb
                Case Is = 1
                    Cell.Value = lr & " " & Cell.Value
                    lr = "2nd Level"
                    lrb = 2
                Case Is = 2
                    Cell.Value = lr & " " & Cell.Value
                    lr = "3rd level"
                    lrb = 3
                Case Is = 3
                    Cell.Value = lr & " " & Cell.Value
            End Select
    End Select
Next

End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299894 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO