1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro, Returning a value in a cell

Discussion in 'Software Development' started by deeearth, Apr 2, 2008.

Thread Status:
Not open for further replies.
  1. deeearth

    deeearth Thread Starter

    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
    m_iColumn = m_iColumn + m_iColumnJump
    'adds 7 to the m_iColumn number
    End If

    If m_iColumn = 3 Then
    Last_Test = "Test run not started."
    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
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/699566

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice