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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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,651
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
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,651
Are these Excel date values or text that look like dates?
 

Mann750

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

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

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top