Suspiciously redundant Excel macro code I need a little help in finding a better solution. In the screen shot below any change to the forecast line I need to find the value in column B. The screen shot is only a sample in my production sheet the row count is currently 4200.

           Column A  Col B   Col C   Col D  Col E 
Row 5              January
Row 6      Prg Desc   SD      FG      GH      DF
Row 7      Forecast    1       2       3       4
Row 8      Actual
Row 9      Compare


My current code does what I need it to, however, I'm sure there is a better method of accomplish my goal. In my production sheet the value in B is in row 5.

CurMonth = target.Offset(5 - target.Row).Value
        If CurMonth = "" Then
                CurMonth = target.Offset((5 - target.Row), -1).Value
            If CurMonth = "" Then
                CurMonth = target.Offset((5 - target.Row), -2).Value
            If CurMonth = "" Then
                CurMonth = target.Offset((5 - target.Row), -3).Value
            End If
            End If

        End If
    Debug.Print CurMonth


Any suggestions would be very appreciated.
Not relevant to your algo, but you shouldn't be using "" like this; use the vbNullString language constant instead, which consumes 0 memory (as opposed to "" which has the overhead of a string).

As for suggestions, in order to find a better way to do something, it's important to first understand what's going on. And proper indentation greatly helps for that:

CurMonth = target.Offset(5 - target.Row).Value
If CurMonth = vbNullString Then
    CurMonth = target.Offset((5 - target.Row), -1).Value
    If CurMonth = vbNullString Then
        CurMonth = target.Offset((5 - target.Row), -2).Value
        If CurMonth = vbNullString Then
            CurMonth = target.Offset((5 - target.Row), -3).Value
        End If
    End If
End If
Debug.Print CurMonth


Something sticks out right away - every new nested level seems to increment the offset, so what you really have is this:

CurMonth = target.Offset((5 - target.Row), n).Value
If CurMonth = vbNullString Then
    n = n - 1
    [and do that again while n &gt; -3]


This looks like a recursive logic - something like this:

Private Function GetCurMonth(ByVal n As Long) As String
    Dim result As String

    result = target.Offset((5 - target.Row), n).Value
    If result = vbNullString And n &gt; -3 Then
        n = n - 1
        result = GetCurMonth(n) 'recursive call
    End If

    GetCurMonth = result
End Sub


Also CurMonth has no reason not to be called CurrentMonth I guess. As Comintern mentioned, it would be easier to give a better review with more [code] context.