VBA Help - Find Function

[ckhoo]

Hi,

I am trying to use the following code on a specific tab of a workbook I am working. The generic code works on other tabs just not on the one referred to in the below code:

'Selects Comparison To Fcst
Sheets("Comparison To Fcst").Activate
Range("D7").Select


'Searches through spreadsheet from the active cell (D7)
Set Found = Cells.Find(What:=Sheets("Comparison to Fcst").Range("B2").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)


'If nothing is found then a dialogue box appears
If Found Is Nothing Then
MsgBox "Not found", vbInformation
Exit Sub
Else

'Selects cell with date in
Found.Select
End If

'Selects Range of Cells to be Copied
Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(18, 0)).Select

The cell B2 has the date 01/10/2016 and the cell I am looking for has the formula =VLOOKUP($B$3&$B$4,'Control Panel'!$E$6:$F$77,2,0) which has the output of 01/10/2016.

I am not sure if the find function can figure out the vlookup as it comes up with the message of Not Found.

Any help on the above would be very much appreciated.

Kind regards,

Chris

 

[ckhoo]

Hi Dave,

Thanks for your comments.

It turns out that it was a formatting issue. I have had to add a helper column on the date look up with a specific format and have had to alter the formulas for the dates in D7 going forward so that are the same. After this the macro ran fine.

Thanks to everyone who contributed, it was very much appreciated.

Kind regards,

Chris

 

[SQUIDD]

your welcome

glad it worked out for you.

Dave