Problem with particular vba Find code

Mann750

Board Regular
Joined
Dec 16, 2009
Messages
72
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.

Code:
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
Code:
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try just

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

Mann750

Board Regular
Joined
Dec 16, 2009
Messages
72
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this

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

Mann750

Board Regular
Joined
Dec 16, 2009
Messages
72

ADVERTISEMENT

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

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Are these Excel date values or text that look like dates?
 

Mann750

Board Regular
Joined
Dec 16, 2009
Messages
72

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Let us check whether the date is being found

Code:
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
How about

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

Watch MrExcel Video

Forum statistics

Threads
1,118,876
Messages
5,574,765
Members
412,617
Latest member
mlharris
Top