Find Date In Row and Paste Value Under The Date

[benntw]

I have a sheet with a date range to put the current progress under it everyday. What I am trying to do is run a macro to take my current percent complete in A2 and look for date in range K2:BS2 that matches the date in cell A4 and place the percent complete as a value in the cell.

 

[JLGWhiz]

Sub t()
Dim fn As Range
    With ActiveSheet
        Set fn = .Range("K2:BS2").Find(Range("A4").Value, , xlValues)
            If Not fn Is Nothing Then
                fn.Offset(1) = .Range("A2").Value
            End If
    End With
End Sub

 

[benntw]

I tried this code it when I stepped through it it skipped over fn.Offset(1) = .Range("A2").Value

 

[esaudie]

I tried it too but didnt get it

 

[JLGWhiz]

For the code to work, there must be a value in A2, A4 and K2:BS2. The code worked fine in a mock-up where those conditions are met. The code assumes all ranges are on the same worksheet. If the it is skipping over the copy/paste line, then the Object Variable was not initialized in the Find statement. That indicates no value matching the value in A4 was found.

 

[JLGWhiz]

BTW, the value in A2 will be placed on row 3, under the found date.

 

[benntw]

I change the value in the range and A4 from a date to a letter. It worked just fine with a letter. The code for some reason does not like a date.

 

[MARK858]

@benntw, what happens if you change

Set fn = .Range("K2:BS2").Find(Range("A4").Value, , xlValues)

to

Set fn = .Range("K2:BS2").Find(CDate(.Range("A4").Value), , xlValues)

 

[benntw]

Tried the CDate code and it still did not like it. I really appreciate the help from both of you. I think I'll use a cheater row since it worked with numbers and not dates.

 

[MARK858]

Out of interest if in a blank cell in column K you put the formula

=ISNUMBER(K2)

and drag it across to column BS do you get TRUE in all the cells?