Serving the Quantitative Finance Community
VBA Code problem
Hi there,I've sat in front of this one for a while but can't see what is wrong. I get a type mismatch error.Any suggestions?Dim firstCocoaRow As Long firstCocoaRow = Worksheets("Risks").Cells.Find(What:="CC", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext).RowAny help appreciatedSteve
VBA Code problem
no errors if it can find CC, but if there's no CC you shouldDIM resset res = worksheets(..).cells.find(..)and test for res not being Nothing... then you can get Row propertybye
VBA Code problem
The problem seems to be when I call the function that this code is written in from a cell, it falls over. Wheras if I call it otherwise, it is fine.
VBA Code problem
Steve,try debugging when the error message occurs and in the debug panel type:? typename(Worksheets("Risks").Cells.Find(What:="CC", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row)This should shed some light on what is being returned!There is some difference between what you can do in code that is executed as part of a calculation (i.e. in a cell) and run via the macro tool. But mainly the difference is in what you are allowed to modify (i.e. cells other than the one calling the code).
VBA Code problem
Hello,I think I've had this problem some time ago.If I understand correctly you are trying to call your VBA function from a cell.If my memory serves me right, I think the problem is as follows:When you hit the return key, after typing the formula name and arguments in the ActiveCell, this cell remains in 'edit' state until a value is returned from the function.In the meanwhile the Cells.Find method cannot operate as it needs all cells to be in idle state.Hope this helps.
VBA Code problem
Good spot gregorios!So make sure that the cell which contains your user defined function is NOT part of the range which you run the find method over.Good luck!