developerWorks  >  Lotus  >  Forums & community  >  Lotus SmartSuite Forum Lotus SmartSuite Forum

Hello all,

I'm attempting to create a function to analyze/compare the cell values from 2 different ranges with the cell values in a table. I was thinking that a function would work better than a sub since a function could be loaded with different ranges for each pass. I have approx. 10 tables to analyze with 5 different ranges to combine.

This is what I've cobbled together so far:

Function Analyze_First (range_1 As Variant, datacells_1 As Variant, datacells_2 As Variant)
' for this test , the ranges are:
' range_1 = TABLE_C and consists of cells A11...H16
' datacells_1 consists of cells A1...J1 and contain values - "0" is a value
' datacells_2 consists of cells A3 ... M3 and contain values - "0" is a value
' Both ranges contain "Blank" cells and when data is entered, may contain cells with "." (a period)
' If data has not been entered into the "datacells", all cell formulas display "-" ( a hyphen)

Set range_1 = [range_1]
Set datacells_1 = [datacells_1]
Set datacells_2 = [datacells_2]


Forall datacells In range_1

If cell.contents = "-" Then
Msgbox "The appropriate Data has not been entered into Range 1 ",48,"Missing Data"
Exit Function
Else
If ("@Isempty (datacells)") =True Then
' move to next cell and start Forall Loop again until all cells in table have been checked.
Else
If cell.contents = "." Then
Selection.Font.FontColor.ColorName = "25% gray"
' move to next cell and start Forall Loop again until all cells in table have been checked.
Else
If cell. cellvalue => "10" Then
Selection.Font.FontColor.ColorName = "20% gray"
' move to next cell and start Forall Loop again until all cells in table have been checked.
Else
If cell.cellvalue = [datacells_1].cellvalue And [datacells_2].cellvalue Then
Selection.Font.FontColor.ColorName = "15% gray"
' move to next cell and start Forall Loop again until all cells in table have been checked.
End If
End If
End If
End If
End If
End Forall
End Function


AND I'm attempting to run it from a sub, and the editor is telling me that it's missing an argument:

Sub test
Analyze_First [(TABLE_C, A1...J1, A3 ... M3)]
End Sub

I reviewed several different examples to get this far, and non of them seem to work correctly. I know that it has to do with how the Forall loop is setup, but don't understand the steps needed.

Is there anyone reading this that would be able to tell me what I'm doing wrong, I'd greatly appreciate the assistance.

Thank you very much.

Go back