Problem with particular vba Find code

[Mann750]

Hello everyone,

I have a (quick) question which I hope someone can answer for me. I have already searched forums for answers but nothing seems to be giving me the correct answer. I have the following code which is supposed to allow me to find a value depending on what is set in another cell.

Dim rng As Range, rng1 As Range
Dim s As Variant
s = Range("B1").Value
Set rng = Range("A5").EntireRow.Cells
Set rng1 = rng.Find(s, rng(rng.Count), xlValues, xlWhole, xlByColumns, xlNext, False)

In cell B1 I have a date "01/01/2011" (which is set by user input) and in row 5 I have dates for each month from "01/01/2010" to "01/12/2012". I need the code to find the date "01/01/2011" and then offset 24 months, affectively to add a new date "01/01/2013". I have experimented with different code to give me the offset using the

Cells(5, Columns.Count).End(xlToLeft).Column

code but I would like the find function to give me the starting position instead.

What needs to be done to correct this error?

I keep the run-time error '91' Object variable or With block variable not set.

 

[VoG]

Try just

Set rng1 = rng.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)

 

[Mann750]

Thanks VoG, that stops the error message from coming up but now how would I incorporate the find function to offset from the found cell 24 columns and then in that cell put a value?

 

[VoG]

Try like this

Set rng1 = rng.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
rng.Offset(, 24).Value = DateAdd("m", 24, rng1.value)

 

[Mann750]

I'm getting the same error as before but now on the part of the code

= DateAdd("m", 24, rng1.Value)

 

[VoG]

Are these Excel date values or text that look like dates?

 

[Mann750]

Apologies for the late reply. They are Excel dates entered as either 01/01/2010 or Jan 2010 etc but formatted to Jan-2010. I am able to do calculations with these dates, for example adding a day, month or year, without a problem using the date function on the worksheet. Hope this helps in solving my problem!!!
Many thanks for your help so far

 

[VoG]

Let us check whether the date is being found

Set rng1 = rng.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
If rng1 Is Nothing Then
    MsgBox "Not found!"
Else
    rng.Offset(, 24).Value = DateAdd("m", 24, rng1.Value)
End If

 

[Mann750]

Unfortunately the msgbox appears! (

 

[VoG]

How about

Set rng1 = rng.Find(what:=CLng(s), LookIn:=xlValues, lookat:=xlWhole)