Finding and Setting Columns

**ashleys.nl** · 10-05-2011, 08:43 AM

Hey Everyone,

I've got columns labelled using two seperate cells. I want to find these columns using a macro so that I can then copy and paste certain rows of information from these columns. The only problem is since the columns are labelled using two cells sometimes key words repeat. So I have tried to create a search and check. However when I try to paste data using methods I have used in the past it doesn't seem to work. I get Run Time Error '1004' Application-Defined or object defined error.

I know I've probably explained this poorly so I've attached a work book and the code below.

Thanks for help in advance

Sub DoubleColumn()

Dim AllocOil As Range
Dim Oil As String
Dim ColAllocOil As Long

Set AllocOil = Worksheets("Sheet1").Range("A5:Q8").Cells.Find(What:="Alloc", LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)
For i = 1 To 10

If AllocOil.Offset(1, 0) = "oil" Then

ColAllocOil = AllocOil.Column
Exit For

Else
Set AllocOil = Worksheets("Sheet1").Range("A5:Q8").Cells.FindNext(After:=AllocOil)
End If

Next i

Worksheets("Sheet1").Cells(1, "A").Value = Worksheets("Sheet1").Cells(8, ColAllocOil).Value

End Sub

**StephenR** · 10-05-2011, 08:49 AM

Why don't you explain in plain English what you are trying to achieve, and then we can correct your code or perhaps suggest alternative methods.

**ashleys.nl** · 10-05-2011, 09:09 AM

Hey Stephen,

I'll take another shot at it. In a logical since what I want to do is find the column labelled "Alloc Oil" . Then from this column I want to select data under that heading. For example in the code I provided I am interested in finding the data in row 8 under the "Alloc Oil" column.

The reason I am using the find function is that sometimes these reports can arrive to me in a different format. With the colums not always being in the same order, however their labelling remains consistent.

So again I want to find specific columns, and then copy and paste data from specific rows under these columns.

**StephenR** · 10-05-2011, 09:53 AM

That's better. Try this:

Sub x()
  
Dim rFind As Range, ColAllocOil As Long
 
With Sheets("Sheet1").Range("A5:Q8")
    Set rFind = .Find(What:="Alloc", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        Do Until rFind.Offset(1) = "Oil"
            Set rFind = .FindNext(rFind)
        Loop
        ColAllocOil = rFind.Column
        Worksheets("Sheet1").Cells(1, "A").Value = Worksheets("Sheet1").Cells(8, ColAllocOil).Value
    End If
End With
     
End Sub