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.
 

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.
Try just

Code:
Set rng1 = rng.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
 
Upvote 0
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?
 
Upvote 0
Try like this

Code:
Set rng1 = rng.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
rng.Offset(, 24).Value = DateAdd("m", 24, rng1.value)
 
Upvote 0
I'm getting the same error as before but now on the part of the code

Code:
= DateAdd("m", 24, rng1.Value)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
How about

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

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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