# Excel Macro, Returning a value in a cell



## deeearth (Apr 2, 2008)

Hi all,
i am trying to create a summary page for a test document, each module of testing has been given its own tab in the workbook and i want to use a macro to:
1, look up the correct tab
2, find the latest test run that has been finished
3, return the test round number, the number of tests run, and the number of tests that passed.
I am using Excel 2003 edition.

I can make the macro the problem is i have to make 3 SEPERATE macros to return the 3 values. I would like to only have one that can return all three values.

the following ths the macro that i have so far:

Option Explicit
Private m_iColumn As Integer 'm_iColumn currently on
Private Const m_iColumnJump = 7 'number of m_iColumns in between test runs
-----------------------------------------------------------------------------------------------------
Function Last_Test(Round_lookup As String)
m_iColumn = 3
Do Until Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Or Worksheets(Round_lookup).Cells(3, m_iColumn) = vbNullString
'runs until finds nothing for a test run
If Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Then
'looks to see if cell contains a testrun if it doesnt then
Else
m_iColumn = m_iColumn + m_iColumnJump
'adds 7 to the m_iColumn number
End If
Loop

If m_iColumn = 3 Then
Last_Test = "Test run not started."
Else
Last_Test = Worksheets(Round_lookup).Cells(1, m_iColumn - 7)
'the last m_iColumn is the name we want
End If
End Function
-------------------------------------------------------------------------------------------------------
This will get me the last round of testing and put it in the place i want. If i were to include the following lines:

"No_of_tests = Worksheets(Number_lookup).Cells(2, m_iColumn - 7)"
"Tests_Passed = Worksheets(Passed_lookup).Cells(4, m_iColumn - 7)"

I can lookup the two other values i want and store them in variables, I just cant print those variables out on the worksheet directly beside Last_Test.

Can anyone help? please


----------

