Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Simple macro loop problem...

    Hi,

    I have made a simple macro that splits the following data

    red blue yellow
    1 2 2
    3 3 1

    into...

    red blue yellow
    1 2 2

    red blue yellow
    3 3 1

    The problem is that I want it to work out how many columns I have using a loop so that I can avoid using the repeat coding. e.g Head_1 = Range...

    I think should be easy but I am pulling my hair out! Thank you to anyone who helps out

    Code:
    Dim c As Range, rng
    Dim Header_1 As String
    Dim Header_2 As String
    Dim Header_3 As String
    
    Header_1 = Range("a1").Value
    Header_2 = Range("b1").Value
    Header_3 = Range("c1").Value
    
    Set rng = Range("b2:b" & Range("b65536").End(xlUp).Row)
    
    For Each c In rng
    If c.Value <> "" And c.Offset(1, 0).Value <> 0 Then
    If c.Value = c.Offset(1, 0).Value Then
    Else
    For i = 1 To 2 Step 1
    c.Offset(1, 0).EntireRow.Insert shift:=xlDown
    Next i
    End If
    End If
    Next c
    For Each c In rng
    If c.Value = "" Then
    c.Offset(1, -1) = Header_1
    c.Offset(1, 0) = Header_2
    c.Offset(1, 1) = Header_3
    End If
    Next c

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looks to me you meant to put this in the Excel forum. Want it moving?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Oops! Yes please

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    There are lots of ways to determine the last column within a range or worksheet. The most reliable is to use the range object's find method, an example would be:

    Code:
    Sub test()
        Dim iCol As Integer
        
        iCol = GetLastColumn(Range("1:1"))
        
        MsgBox "The last used column in range " & Range("1:1").Address & " is " & iCol
    End Sub
    
    
    Private Function GetLastColumn(ByRef rngToCheck As Range) As Integer
        Dim rngFound As Range
        
        With rngToCheck
            Set rngFound = .Find(what:="*", after:=.Cells(1), searchorder:=xlByColumns, searchdirection:=xlPrevious)
        End With
        
        If rngFound Is Nothing Then
            GetLastColumn = 1
        Else
            GetLastColumn = rngFound.Column
        End If
    End Function
    Hope that helps...

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    Colin,

    Thankyou for that. I will have a look today. Much appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •