Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 24th, 2011, 12:37 AM
Registered User
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need help traversing

Hello all,
I need help writing a code for traversing along a column in a specific order. Im not used to Excel and this is seeming very confusing. I would really appreciate any kind of help/suggestions/tips.

I have 4 specific columns that I'm gonna need. they are:
1> Pieces
2> Phour
3> OEE
4> Saw5
(They are all in different sheets)

Now there are multiple entries in each column and I need help traversing through them.
The column of "Saw5" will have an "X" marked in certain entries. Now what I need is to get the corresponding entries in the other columns for that specific product.

For example: If saw theres an "X" marked in the 3rd entry of "Saw5", what I need is to extract the values in the 3rd entry of "Pieces", "Phour" and "OEE".
Then check further and if saw theres an "X" marked in the 5rd entry of "Saw5", what I need is to extract the values in the 5rd entry of "Pieces", "Phour" and "OEE".
and so on...

So basically, I need to check for "X" in the "Saw5" and then find the corresponding values in the other columns and do certain calculations based on that...

If its required, the formula that Im going to use later is:
(If theres an "X")
then -> (Pieces / Phour) / (30 * 24 * OEE)
I will save this value in a new column created, it does not need to be in order...

Please help me out.
Thank you.
Old April 24th, 2011, 12:25 PM
Friend of Wrox
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts

I'll call your sheets by their relevant columns. You'll have to adjust the sheet names accordingly. Go to the Visual Basic Editor and double-click on the sheet that has the "Saw5" column. Paste this code into it:
Option Explicit

'the sheets we'll be working with
Dim shtSaw5 As Worksheet
Dim shtPieces As Worksheet
Dim shtPhour As Worksheet
Dim shtOEE As Worksheet
Dim shtResults As Worksheet 'this is where the results will go
'the relevant columns on each of these sheets
Dim lSawCol As Long
Dim lPieceCol As Long
Dim lPhourCol As Long
Dim lOeeCol As Long
Dim lResultCol As Long
'this will hold the number of the row with the "X"
Dim lRowNum As Long
'where the result is gonna go
Dim lNextResultRow As Long

'for the numbers needed to make calculations
Dim dPieces As Double
Dim dPhour As Double 'not sure what how that time is stored. VBA does have a date/time variable you could use which...
''looks like this:
'Dim dtPhour As Date 'holds both date and time amounts
Dim dOee As Double

Private Sub Worksheet_Change(ByVal Target As Range)
'this will update the other sheet automatically as soon as someone enters an "X" in SheetSaw5

'if the change occured in the Saw5 colum...
If Target.Column = 1 And LCase(Target.Value) = "x" Then 'do this stuff (i put Saw5 in column one of my mock workbook)

    If shtSaw5 Is Nothing Then
        'set the worksheets we'll be using. It would be better to do this in the "ThisWorkbook" module under the "Workbook_Open" event. For simplicity i'll do it here and run a quick if so that it won't keep setting the variables.
        'The names of the sheet that are between the quotation marks is what you need to change so that it matches your workbook...
        Set shtSaw5 = Sheets("SheetSaw5")
        Set shtPieces = Sheets("SheetPieces")
        Set shtPhour = Sheets("SheetPhour")
        Set shtOEE = Sheets("SheetOEE")
        Set shtResults = Sheets("SheetResults")
        'set the columns we'll be working with. I've put these things in column "A" Adjust as needed. Column "B" = 2... etc.
        lPieceCol = 1
        lPhourCol = 1
        lOeeCol = 1
        lResultCol = 1
    End If
    'get the row the that was just changed
    lRowNum = Target.Row
    'get the next available row in the results sheet (there's a better way to do this, but for simplicity...)
    'this assumes you have no blank rows over the Header column in the results field
    lNextResultRow = WorksheetFunction.CountA(shtResults.Range("A:A")) + 1 'add more than one if you have blank rows
    '(Pieces / Phour) / (30 * 24 * OEE)
    'get the numbers needed
    dPieces = shtPieces.Cells(lRowNum, lPieceCol)
    dPhour = shtPhour.Cells(lRowNum, lPhourCol)
    dOee = shtOEE.Cells(lRowNum, lOeeCol)
    'if Phour is a non-date/time number, which is what it looks like you're using...
    shtResults.Cells(lNextResultRow, lResultCol) = (dPieces / dPhour) / (30 * 24 * dOee)
End If

End Sub

Last edited by mtranchi; April 24th, 2011 at 12:29 PM..

Similar Threads
Thread Thread Starter Forum Replies Last Post
traversing up the parent tree until condition dexter62 XSLT 4 November 24th, 2009 05:08 AM
Recordset Traversing: biswa VB Databases Basics 1 October 9th, 2009 06:06 PM
Traversing UP my hierarchy in XPath grant6607 XSLT 1 May 11th, 2005 10:02 AM
XML Traversing using Navigator and Iterator classe jasim XML 1 July 18th, 2003 03:29 AM
passing attribute info while traversing nodes Ashley XML 1 June 20th, 2003 07:42 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.