Results 1 to 1 of 1

calculation using reference to cells based on conditions

Threaded View

shrimic calculation using reference... 02-16-2012, 12:32 PM
  1. #1
    Registered User
    Join Date
    02-09-2012
    Location
    Manassas, Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    30

    calculation using reference to cells based on conditions

    Hello Friends,
    I am trying to develop a macro which does calculations (using references to cells) based on conditions..
    My problem definition is:
    if there is a match between Column A of DRAM and Reference1 sheets, I must look for the row which has the value "WS Group Totals:" in Column B of DRAM Sheet and assign the Column D cell intersecting the row containing "WS Group Totals" to 'Cell3'. Then I have to look if there is a match between Column A of DRAM and Reference1 sheets and must look for the row in Column A of Reference1 where there is a match and I have to assign the "E" and "H" column intersection of the rows to 'Cell5' and 'Cell6' respectively and then I have to calculate the formula '(((10080*cell6*cell3)/(0.98*cell5)))+Worksheet("DRAM").Range("D19").Value' and assign that value to cell D20 and progressively till the end of row is reached, each time offsetting the 'Cell3' value by a column when 'Cell5' and 'Cell6' values remains the same..Actually there are two Workstation Groups: 'IMP_Plasma_PLAD_B2H6' and 'IMP_PLASMA_PLAD_BF3' for which a match of Column A in DRAM and Reference1 sheets will be found. the results for both of them are needed and placed below one another
    I have written a macro which is partly working. For the 1st worksatation group it is taking the 'Cell5' and 'Cell6' values of 2nd Workstation group. I am not able to figure out where I am erring. I have given my code below and have attached the original worksheet for your reference. I am very new to coding and I am really thankful to any help you can provide....
    Option Explicit
    
    Sub DRAMSummary()
    
    Dim PartRngWorkbook1DRAMSheeta As Range, PartRngWorkbook1DRAMSheetb As Range, PartRngWorkbook1Reference1Sheete As Range
    Dim cs As Variant, Cell1 As Long, Cell2 As Long, Cell3 As Variant, Cell4 As Long, Cell5 As Variant
    Dim Cell6 As Variant, PartRngWorkbook1Reference1Sheeta As Range, ab As Variant, cl As Variant
    
    Set PartRngWorkbook1DRAMSheeta = Worksheets("DRAM").Range("A2", Worksheets("DRAM").Range("A65536").End(xlUp))
    Set PartRngWorkbook1DRAMSheetb = Worksheets("DRAM").Range("B1", Worksheets("DRAM").Range("B65536").End(xlUp))
    Set PartRngWorkbook1Reference1Sheete = Worksheets("Reference1").Range("E1", Worksheets("Reference1").Range("E65536").End(xlUp))
    Set PartRngWorkbook1Reference1Sheeta = Worksheets("Reference1").Range("A1", Worksheets("Reference1").Range("A65536").End(xlUp))
    
    
    For Each cs In PartRngWorkbook1DRAMSheeta
        
        If IsNumeric(Application.Match(cs.Value, PartRngWorkbook1Reference1Sheeta, 0)) Then
            Cell1 = PartRngWorkbook1DRAMSheetb.Find("WS Group Totals:").Row
            Cell3 = Worksheets("DRAM").Range("D" & Cell1).Value
            
            
        End If
    Next cs
    For Each cl In PartRngWorkbook1DRAMSheeta
        For Each ab In PartRngWorkbook1Reference1Sheeta
            If cl = ab Then
                Cell4 = PartRngWorkbook1Reference1Sheeta.Find(ab.Value).Row
                Cell5 = Worksheets("Reference1").Range("E" & Cell4).Value
                Cell6 = Worksheets("Reference1").Range("H" & Cell4).Value
                
                Worksheets("DRAM").Range("D20").Value = (((10080 * Cell6 * Cell3) / (Cell5 * 0.98))) + Worksheets("DRAM").Range("D19").Value
            End If
            
        Next ab
    Next cl
     
        
    'For Each ab In PartRngWorkbook1DRAMSheeta
        'If IsNumeric(Application.Match(ab.Value, PartRngWorkbook1Reference1Sheeta, 0)) Then
            'Cell4 = PartRngWorkbook1Reference1Sheeta.Find(ab.Value).Row
            'Cell5 = Worksheets("Reference1").Range("E" & Cell4).Value
            'Cell6 = Worksheets("Reference1").Range("H" & Cell4).Value            
            
    
        
    
    
    End Sub
    Attached Files Attached Files
    Last edited by shrimic; 02-16-2012 at 01:18 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1