Find Date In Row and Paste Value Under The Date

benntw

Board Regular
Joined
Feb 17, 2014
Messages
222
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Code:
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
 
Upvote 0
I tried this code it when I stepped through it it skipped over fn.Offset(1) = .Range("A2").Value
 
Upvote 0
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.
 
Last edited:
Upvote 0
BTW, the value in A2 will be placed on row 3, under the found date.
 
Upvote 0
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.
 
Upvote 0
@benntw, what happens if you change
Code:
Set fn = .Range("K2:BS2").Find(Range("A4").Value, , xlValues)
to
Code:
Set fn = .Range("K2:BS2").Find(CDate(.Range("A4").Value), , xlValues)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top