Thread: Checking Offset Assumptions

Instead of using .offset with the Mouse/day column, why not just find the column number that "Cat" is in and use that directly? Assuming, of course, that you won't have problems with the word "cat" coming up multiple times in the spreadsheet, in which case you'll want to restrict the search range from .cells to something smaller.

Sub ColumnMatchTest()


    Dim Cat                             As Range
    Dim ColCat                          As Long
    Dim RowCat                          As Long


    Set Cat = Worksheets("Sheet1").Cells.Find(What:="Cat", LookIn:=xlFormulas, LookAt:=xlWhole, _
                                              SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                                              SearchFormat:=False)
    If Not Cat Is Nothing Then
        ColCat = Cat.Column
        RowCat = Cat.Row
    Else
        MsgBox ("Can't find cat")
        Exit Sub
    End If


    'obviously, this will just put the word Cat into the sheet, but I think you get the idea.
    Worksheets("Sheet2").Cells(1, "D").Value = Worksheets("Sheet1").Cells(RowCat, ColCat).Value

End Sub

Ahhh okay, I can certainly use something like this... I didn't know the .Column and .Row funtions existed... I'll have a go at this code later today... but it looks great! Thanks for the help

Thanks Chris!

I've taken what you had suggested and added a way to indentify the row as well. but it works great! this is the final code I've created... in case anyone else comes accross this problem

Thanks again Chris

Sub ColumnMatchTest()


    Dim Cat                             As Range
    Dim ColCat                          As Long
    Dim MousePDay                       As Range
    Dim RowMousePDay                    As Long


    Set Cat = Worksheets("Sheet1").Cells.Find(What:="Cat", LookIn:=xlFormulas, LookAt:=xlWhole, _
                                              SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                                              SearchFormat:=False)
    If Not Cat Is Nothing Then
        ColCat = Cat.Column

    Else
        MsgBox ("Can't find cat")
        Exit Sub
    End If
    Set MousePDay = Worksheets("Sheet1").Cells.Find(What:="Mouse/Day", LookIn:=xlFormulas, LookAt:=xlWhole, _
                                              SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                                              SearchFormat:=False)
    If Not MousePDay Is Nothing Then
        RowMousePDay = MousePDay.Row

    Else
        MsgBox ("Can't find 'Mouse/Day'")
        Exit Sub
    End If
    'obviously, this will just put the word Cat into the sheet, but I think you get the idea.
    Worksheets("Sheet2").Cells(1, "D").Value = Worksheets("Sheet1").Cells(RowMousePDay, ColCat).Value
    
        
End Sub