Posts by shrimic

Re: Incrementing two For-Next loops at the same time

I am sorry..but once I was advised to post links on ozgrid if I couldn't find answer from other forum...they said it was bad to post twice..sorry

http://www.excelforum.com/exce…oop-at-the-same-time.html

Re: Adding a row values with the values in the rows below

oh sorry Stephen...I donna this rule..

Re: Adding a row values with the values in the rows below

Thank you very much both Messruth and Stephen...

Hi friends,

I have a small problem with a macro..can somebody please help...

I have to add a row with all the other rows below it...

Say:

Dram 100 150 200 300
Nand 50 75 80 90
Nor 11 22 33 44

I have to add the Dram row values with the below row values....
I have to get the output like...

Dram 100 150 200 300
Nand 150 225 280 390
Nor 111 172 233 344

can somebody help me with this..thank you very much...

Re: Application or Object defined error

Sorry cyTop

Hi Friends,

I need to look for a match between Column A of 'DRAM' worksheet and Column A of 'Refer' worksheet and if a match is found then the row containing the match should be stored in a variable 'Cell1' and then my code must look for a string 'WS Group Totals:" in Column B of 'DRAM' sheet and the row containing the string must be stored in a variable Cell2. In the row containing Cell2 the values from Column D till the row end must be multiplied by 10080 and the result must be placed two rows below the last populated row in their respective columns. Once that is done my code must look for the next match of Column A in 'DRAM' and COLUMN A in 'Refer' worksheets and must do the calculation as the same as above. I have written the below code for the purpose but I am repeatedly getting Application or Object defined error...can someone please help me figure out the error...thank you very much..I have attached the original Workbook for your reference...

Option Explicit
 
 
Sub DRSummary()
 
Dim PartRngWorkbook1DRAMSheeta As Range, PartRngWorkbook1DRAMSheetb As Range
Dim cs As Variant, Cell1 As Long, Cell2 As Long, rngMultiply As Range, Cell4 As Long, Cell5 As Long
Dim Cell6 As Long, PartRngWorkbook1Reference1 As Range, a As Variant, wsActive As Worksheet, intCols As Integer, rngValue As Range
Const intMultiplier As Integer = 10080
Dim ab As Worksheet, lastRow As Long
 
Set PartRngWorkbook1DRAMSheeta = Worksheets("DRAM").Range("A3", Worksheets("DRAM").Range("A65536").End(xlUp))
Set PartRngWorkbook1DRAMSheetb = Worksheets("DRAM").Range("B", Worksheets("DRAM").Range("B65536").End(xlUp))
Set PartRngWorkbook1Reference1 = Worksheets("Refer").Range("A", Worksheets("Refer").Range("A65536").End(xlUp))
Set ab = Worksheets("DRAM")
 
For Each cs In PartRngWorkbook1DRAMSheeta
   
    If IsNumeric(Application.Match(cs.Value, PartRngWorkbook1Reference1, 0)) Then
        Cell1 = PartRngWorkbook1DRAMSheeta.Find(cs.Value).Row
      
        Cell2 = PartRngWorkbook1DRAMSheetb.Find("WS Group Totals:", Range("B", Cell1)).Row
       
     
       With ab
            intCols = .Cells(Cell2, .Columns.Count).End(xlToLeft).Column
            lastRow = .Rows(.Rows.Count).Row
                Set rngMultiply = .Range(.Cells(Cell2, 4), .Cells(Cell2, intCols))
                For Each rngValue In rngMultiply
                    .Cells(.Rows.Count, rngValue.Column).End(xlUp).Offset(2, 0).Value = intMultiplier * rngValue.Value
                Next
        End With
    End If
Next cs
End Sub

Re: Offsetting columns and using appropriate cell values during calculations

Thank you Batman...your help surely solves my problem...greatly appreciated...thank you very much....

Re: Offsetting columns and using appropriate cell values during calculations

Thanks for your time dyland...to be very honest I am writing a huge code where the part which I requested for assistance is a small part...as the code I am writing is huge I tried to simplify my problem to exactly to what I had problems in...Actually I had to use D9 value in a huge formula and progressively each E9 and F9...till I reach the end of the line...and this I had to do in a slightly different way for a large number of worksheets with different calculations...If my question was a bit confusing..believe me thats the best I could do to simplify my problem..thanks for your help Sir...

Hello friends,

I am trying to code a macro for the following scenario:
1. Everytime I run the macro, it has to take the value in column "D" 1st row (D1) and multiply it with 100 and then put the result in the cell below the last filled cell in D column and then it has to offset by a column to E and then multiply the value in E1 with 100 and put it in the cell after the last filled cell in column E and then it should do the same above stated steps with column F and H, I till the it reaches the lastfilled cell in row 1...can somebody help me with this...thanks in advance..

Re: calculate value using cells referenced based on conditions

Unfortunately the data for this macro is from another macro and the rows can change drastically...thank you...

Re: calculate value using cells referenced based on conditions

ya it can be done more simply as you said but my boss wants to run a macro and all calculations done for him....ya basically what I meant in the confusing part was...if the "WS Groups" fall in the 9 the row , then it should be assigned the cell address D9 and the value in D9 must be assigned to a variable named "Cell3"..

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