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]

Maybe omit the LookIn altogether...

I've used Find with Lookin:=xlValues to find dates before, but when they were in a column, not a row.

 

[Mann750]

Thanks guys for the feedback, really appreciate it. I will try omitting the LookIn part of the function and see if that makes any difference.

I don't suppose either of you could help me with my earlier post (see below) Thanks again for all your help!!!

Apologies but I have one more bit of code that I need help on, continuing on from what has been posted before.

If I enter Dec-2011 in to B1 I would like the code to add December 24 months on (as it already does with the code VoG has posted) but what I also need is to add a "Total" column to the end of that. For example:

Dec-2011 Total Jan-2012 ... Dec-2013 Total

I have experimented with

    If DatePart("m", Cells(5, LC).Value) = "12" Then
    Cells(5, LC + 1).Value = "Total"

but I am not sure where to put it as sometimes I have to click twice on the command button for it to add "Total" and sometimes I get an error. I am sure it is just my placement of the code that is causing problems so hopefully this should be a quick response. The full code (as it stands) is below:

Private Sub cmdAddDate_Click()
Dim c As Range, LC As Long
LC = Cells(5, Columns.Count).End(xlToLeft).Column
For Each c In Range(Cells(5, 3), Cells(5, LC))
 
    If c.Value = Range("B1").Value Then
        With c.Offset(, 26)
            .Value = DateAdd("m", 24, c.Value)
            .NumberFormat = "mmm-yyyy"
        End With
    End If
 
    If DatePart("m", Cells(5, LC).Value) = "12" Then
    Cells(5, LC + 1).Value = "Total"
 
    Exit Sub
    End If
 
Next c
MsgBox "Not found!"
End Sub

Thanks in advance for your wisdom!

 

[Mann750]

It's ok guys, thanks for your help but I realised what I was doing wrong

For those who would like to know the final code then see below:

Private Sub cmdAddDate_Click()
Dim rng As Range, rng1 As Range
Dim s As Variant
Dim LC As Long
s = Range("B1").Value
LC = Cells(5, Columns.Count).End(xlToLeft).Column
Set rng = Range("A5").EntireRow.Cells
Set rng1 = rng.Find(what:=s, LookIn:=xlFormulas, lookat:=xlWhole)
    If rng1 Is Nothing Then
        MsgBox "Not found!"
    
    ElseIf DatePart("m", rng1.Value) = "12" Then
        rng1.Offset(, 26).Value = DateAdd("m", 24, rng1.Value)
        rng1.Offset(, 27).Value = "Total"
    
    Else
        rng1.Offset(, 26).Value = DateAdd("m", 24, rng1.Value)
    
    End If
End Sub