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

 

[steve the fish]

Try looking in xlValues

 

[ckhoo]

Hi Steve the fish,

Thanks for your reply.

I have tried changing the LookIn:= to xlValues but it is still coming up with "Not Found".

If I hard code the cell so it says 01/10/2016 then it finds it.

 

[JoeMo]

Hi Steve the fish,

Thanks for your reply.

I have tried changing the LookIn:= to xlValues but it is still coming up with "Not Found".

If I hard code the cell so it says 01/10/2016 then it finds it.

My guess is one of your dates (B2 or what Vlkup returns) is a number and the other is text. You can check quickly by using =ISNUMBER(B2) and doing the same for the cell vlkup is in.

EDIT: another possibility is that both cells have dates, but one is just an integer date and the other is date & time with the cell formatted to only show the date portion.

 

[steve the fish]

Is it a true date? Format the cell to number. Does it change its appearance?

 

[ckhoo]

Hi JoeMo,

I have checked and both cells return a value of "True". I have also converted both cells to number and they are both integers.

Any other ideas what could be happening?

 

[JoeMo]

Hi JoeMo,

I have checked and both cells return a value of "True". I have also converted both cells to number and they are both integers.

Any other ideas what could be happening?

Assuming the two integers are identical, try changing Lookin:= xlPart to Lookin:= xlWhole

 

[ckhoo]

I have tried changing that part of the code to xlWhole but that still produces an error.

Do you know how I can upload a file as I have a test file? which may be of use.

 

[JoeMo]

I have tried changing that part of the code to xlWhole but that still produces an error.

Do you know how I can upload a file as I have a test file? which may be of use.

There is no mechanism for upload on this forum. You can put it in Dropbox or a similar application and add a link here. Personally, I don't download from the internet, but maybe someone else here will.

 

[SQUIDD]

Hi

I have run your code.
Excel is selecting range B2 at this point Found.Select

But i believe your code is supposed to search after D7?

Not sure if that is of any use, other than that the code works for me, so would assume format issue.

Dave