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.

 

[Mann750]

Same error again! If I sent my workbook would that help you to hopefully solve the problem?

 

[VoG]

Can you upload it to www.box.net and provide a link in this thread. Remove any sensitive information first!

 

[Mann750]

Never used the site before so here goes:

http://www.box.net/shared/9kx9q002b3

 

[VoG]

A complete mystery (to me anyway) - I just cannot get the Find to work.

As you are not searching thousands of cells give this a go

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, 4), Cells(5, LC))
    If c.Value = Range("B1").Value Then
        With c.Offset(, 24)
            .Value = DateAdd("m", 24, c.Value)
            .NumberFormat = "mmm-yyyy"
        End With
        Exit Sub
    End If
Next c
MsgBox "Not found!"
End Sub

 

[Mann750]

So I'm not the only one thinking such a simple find problem is turning in to a mystery. I shall try the formula and get back to you soon. Thanks again for time and help!

If anyone else has any ideas please feel free to contribute, it will be greatly appreciated!!!

 

[Mann750]

VoG,

Thanks for your help, that code does the trick (as you're probably aware)! Would be nice to know why the "find" function doesn't work but best to get on with the code that does work!

 

[Mann750]

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!

 

[John_w]

So I'm not the only one thinking such a simple find problem is turning in to a mystery. I shall try the formula and get back to you soon. Thanks again for time and help!

If anyone else has any ideas please feel free to contribute, it will be greatly appreciated!!!

This works for me (using LookIn:=xlFormulas instead of xlValues):

Private Sub cmdAddDate_Click()

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(what:=s, LookIn:=xlFormulas, lookat:=xlWhole)
If rng1 Is Nothing Then
    MsgBox "Not found!"
Else
    rng1.Offset(, 24).Value = DateAdd("m", 24, rng1.Value)
End If

End Sub

 

[Mann750]

Such a simple fix! Thanks John w.

I don't suppose you could explain why xlFormulas works instead of xlValues in this case? I am not a complete novice in VBA but I would still like to understand the how's and why's of coding.

 

[John_w]

I think Excel dates are considered to be formulas. I Googled 'excel vba find date' and came up with http://www.cpearson.com/excel/DateTimeVBA.htm