Checking Offset Assumptions

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

Hey Everyone,

I'm trying to create a check in my Excel Macro to check if my assumptions are correct. For example in a given worksheet I want to find the row labeled "Mouse/Day". Once I've found this row I assume the column "Cat" is one column to the right. But what if it wasn't? what if the columns had been input in a wrong order. Instead of the cat column being at an offset of ( 0, 1) the dog column was there instead. Is there a creative way to check ?

Thanks for your help everyone! You've all been great these past weeks.

Sub ColumnMatchTest()


Dim Cat As Range
Dim MousePDay As Range

   
    Set MousePDay = Worksheets("Sheet1").Cells.Find(What:="Mouse/Day", LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)
Worksheets("Sheet2").Cells(1, "D").Value = MousePDay.Offset(, 1).Value
        

End Sub

**ChrisOswald** · 09-13-2011, 03:56 PM

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

**ashleys.nl** · 09-14-2011, 07:38 AM

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

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

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