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
Bookmarks