If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Simple macro loop problem...

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 04-17-09, 09:23
didster didster is offline
Registered User
 
Join Date: Apr 2009
Posts: 3
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
Reply With Quote
  #2 (permalink)  
Old 04-17-09, 09:58
pootle flump pootle flump is offline
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
Looks to me you meant to put this in the Excel forum. Want it moving?
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #3 (permalink)  
Old 04-17-09, 10:42
didster didster is offline
Registered User
 
Join Date: Apr 2009
Posts: 3
Oops! Yes please
Reply With Quote
  #4 (permalink)  
Old 04-17-09, 11:52
Colin Legg Colin Legg is offline
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 495
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...
Reply With Quote
  #5 (permalink)  
Old 04-18-09, 04:02
didster didster is offline
Registered User
 
Join Date: Apr 2009
Posts: 3
Colin,

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

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On