Guys,

I need some help with a find function.

I am writing some VBA code;

Range("AH7:AP12").Select
Selection.Find(What:="(ActiveCell.range("ag3").Select)", After:=ActiveCell, LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy

I am basically trying to find the value of Cell AG3 in the range AH7:AP12, and then copying the heading from the column that the value is found in.

The code works when I replace the target cell to find with a value, but I need it to be dynamic, as it will be used in a loop to find different values each time it is executed.

What am I doing wrong...

Any help would be greatly appreciated.

Scott

Well I've never done this :D and have no test data but I don't like the look of
Selection.Find(What:="(ActiveCell.range("ag3").Sel ect)", After:=ActiveCell

having pasted the code into the vb editor it all goes red, reading help the After keyword is telling Excel to start the search after the active cell, isn't that now AG3 ? I would place the value of AG3 into a variable at the start and search using that.

Brian

Well I've never done this :D and have no test data but I don't like the look of
Selection.Find(What:="(ActiveCell.range("ag3").Sel ect)", After:=ActiveCell

having pasted the code into the vb editor it all goes red, reading help the After keyword is telling Excel to start the search after the active cell, isn't that now AG3 ? I would place the value of AG3 into a variable at the start and search using that.

Brian

Brian,

I have tried this and it works.....conditionally....

It will only work if it can match the cell values without any formatting, and as the values change in length it does not always match the cells.

I have tried formatting both the target range and the Find cell to be the same but this does not work.

I have copied below a representation of my Find cell and my target cells, as you can see the target cells have have the Find cell amongst them.

But when I declare the Find cell as the variant, it returns its full value of "3444004.16387351" and although in the Target cells the full value is the same because it cannot find the match due to the formatting it returns an error !!

3444004.164 (Find Cell)

(Target cells)
0.00 0.10 0.20 0.30 0.40 0.50
0.05 4088889.736 3698889.682 3529201.42 3462719.216 3444004.164
0.10 3929635.213 3680630.406 3577735.539 3540298.619 3531634.722
0.15 3877740.968 3721215.427 3657892.954 3634301.804 3627354.179
0.20 3905145.648 3804108.602 3759492.323 3738756.456 3727665.524
0.25 3992963.764 3915733.49 3874686.084 3849221.449 3829993.121
0.30 4120236.208 4044897.772 3997594.182 3962446.044 3932449.841

Have you any ideas?

This is my code.

Columns("ag").AutoFit
'Creates a string to search with and assigns a double byte format
Dim StrValue As Double
' Convertes the string into a Double Decimal point number format
StrValue = CDec(Range("ag3").Value)


Columns("ah:ap").AutoFit
Range("AH7:AP12").Select
Selection.Find(What:=StrValue, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Select

Selection.End(xlUp).Select

But when I declare the Find cell as the variant, it returns its full value of "3444004.16387351" and although in the Target cells the full value is the same because it cannot find the match due to the formatting it returns an error !!

3444004.164 (Find Cell)

I find this confusing, cannot see what is going on.
Is AG3 a calculated cell which you display to 3 dec places?
Can you Round it, are the target cells Rounded?
Why do you have to convert it, and if converting to Double decimal why use Cdec not Cdbl ?
Although I am unlikely to be on the forum tomorrow perhaps you could post your workbook.

Brian

I find this confusing, cannot see what is going on.
Is AG3 a calculated cell which you display to 3 dec places?
Can you Round it, are the target cells Rounded?
Why do you have to convert it, and if converting to Double decimal why use Cdec not Cdbl ?
Although I am unlikely to be on the forum tomorrow perhaps you could post your workbook.

Brian


Brian,

Thanks for your help, but I have been able to solve this on my own.

It was a formating issue.

I have basically converted both sets of numbers to text formats, and have declared the variable to be a text format, which in turn worked.

It looked likely to be a formatting issue, thanks for the feedback.

Brian