Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a function within a macro
Hi,
I have a worksheet called SDB which consists of a row (1) of column headings and then a row (2) of cell values/references/formulas etc. a second sheet called Data in the same workbook holds data values from another source which need to be organised/formatted in a diferent way (i.e. this is what the SDB sheet does). My macro called SDB_copy selects row 2 of SDB and copies down to the last lastrow of data (the lastrow is found using the following line of code: lastrow = Sheets("Data").Cells(Cells.Rows.Count, "A").End(xlUp).row) SDB has some cells which are conditionally formatted to have a red background on certain conditions. I have a couple of functions that 1. identify the colour index (CFColorindex) and 2. count the number of cells which have the specified condition (CFColorCount). I can call the functions from a cell in the worksheet using: =CFColorCount(Z2:Z76,3) which gives the right answer. What I want to do is call the function from my SDB_copy macro and display the result in a message box, for example MsgBox("Please correct errors -" answer) So far I have tried various ways of calling the function from within my macro, such as: Dim Res As Variant Res = Application.Function.CFColorCount("Z2:Z76", 3) MsgBox ("Please Rectify All Highlighted Records - " & Res) but when I try to run the macro I get an error suggesting that the : in the range is unexpected. I would also like to be able to use the lastrow facility such that the range to be counted is ("A2:A" & lastrow). I know this is probably really simple but I just cant get the coding right, all help is gratefully received! I apologise if my explanation is a bit long, thanks in advance Kind regards, Amy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling Function in Add-in | Excel Programming | |||
Calling VBA Function | Excel Programming | |||
calling worksheet function from a macro | Excel Worksheet Functions | |||
Calling an Excel function from a VBA macro | Excel Programming | |||
Calling up the Solver function in a VBA macro | Excel Programming |