Cell address of matching cell in a row

Question

I have a value in Cell B32 that I would like to be able to change week by week. For a new week, I have a macro that copies and pastes data into a file, but the location of the copy/paste changes each week.

The part of the code that is relevant is here:

Dim Week As String
Dim FiscalPeriod As String

Windows("Personal.xls").Activate
Sheets("Store Info").Select
Week = Range("B32").Value

Windows("Budget Load Check.xls").Activate
Sheets("Fcst PT").Select
Rows("4:4").Select
Selection.Find(What:=Week, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
FiscalPeriod = ActiveCell.Address

It is erroring out at Selection.Find(What:=Week

How do I tell it to search for the value in Row 4, then figure out the address of the active cell and label is as 'FiscalPeriod'. Once I do that, I can do a series of cell offsets to paste everything in the right spot.

Answer 1

I'm going to guess that you are looking to find an exact match to a numerical week and that LookAt:=xlPart was just left over from a previous search.

Dim Week As String, FiscalPeriod As String
Week = Workbooks("Personal.xls").Sheets("Store Info").Range("B32").Value
Windows("Personal.xls").Activate
With ActiveWorkbook.Sheets("Fcst PT")
    If Not IsError(Application.Match(Week, .Rows(4), 0)) Then
        FiscalPeriod = .Cells(4, Application.Match(Week, .Rows(4), 0)).Address
    Else
        FiscalPeriod = "not found"
    End If
End With