• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help in old macro file

U

ushaanu

Member
HI all,

Greetings!!

I have one macro file . I wants add some changes on same file,
Like . if P Name contain C400,C9000 then MC and BC calculation change ,if C400,C9000
Than Calculations should be MC has to be subtract from DA3 and BC has to be subtract from NC3.

Thanks
Anu
 

Attachments

  • EXP File.xlsm
    967.3 KB · Views: 5
Please address the questions raised in the duplicated file that has been closed. Please explain directly what cell value is to be subtracted from what cell. Your prior response did not do that. Please be specific as I am not a mind reader.
 
HI Alan sir,
I am using one macro file . it`s perfectly working,but now I need add one condition in same macro file .

New condition is : if in my Raw data worksheet column E having some specific model like C400,n9000,q10000 .
than calculation method id different.

I need in my Raw data worksheet column E having some specific model like C400,n9000,q10000 .
than calculation method id different ,

Method is DA3-BC=DA3
NC3-MC=NC3

example file enclosed SD worksheet is the required result sheet After Running Macro code.

Thanks
 

Attachments

  • EXP File.xlsm
    965.8 KB · Views: 4
Here is some code that should do what you described:

Code: 
Option Explicit

Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Raw Data")
    Set s2 = Sheets("SD")
    Dim i As Long, lr As Long, lrS As Long
    lr = s1.Range("E" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 8 To lr
        lrS = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("E" & i), "C400") > 0 Then
            s2.Range("A" & lrS + 1) = s1.Range("A" & i)
            s2.Range("B" & lrS + 1) = s1.Range("C" & i)
            s2.Range("C" & lrS + 1) = s1.Range("D" & i) & "-DA3"
            s2.Range("D" & lrS + 1) = s1.Range("I" & i) - s1.Range("N" & i)
            s2.Range("E" & lrS + 1) = s1.Range("G" & i)
            s2.Range("A" & lrS + 2) = s1.Range("A" & i)
            s2.Range("B" & lrS + 2) = s1.Range("C" & i)
            s2.Range("C" & lrS + 2) = s1.Range("D" & i) & "-NC3"
            s2.Range("D" & lrS + 2) = s1.Range("K" & i) - s1.Range("M" & i)
            s2.Range("E" & lrS + 2) = s1.Range("G" & i)
        ElseIf InStr(s1.Range("E" & i), "n9000") > 0 Then
            s2.Range("A" & lrS + 1) = s1.Range("A" & i)
            s2.Range("B" & lrS + 1) = s1.Range("C" & i)
            s2.Range("C" & lrS + 1) = s1.Range("D" & i) & "-DA3"
            s2.Range("D" & lrS + 1) = s1.Range("I" & i) - s1.Range("N" & i)
            s2.Range("E" & lrS + 1) = s1.Range("G" & i)
            s2.Range("A" & lrS + 2) = s1.Range("A" & i)
            s2.Range("B" & lrS + 2) = s1.Range("C" & i)
            s2.Range("C" & lrS + 2) = s1.Range("D" & i) & "-NC3"
            s2.Range("D" & lrS + 2) = s1.Range("K" & i) - s1.Range("M" & i)
            s2.Range("E" & lrS + 2) = s1.Range("G" & i)
        ElseIf InStr(s1.Range("E" & i), "q10000") > 0 Then
            s2.Range("A" & lrS + 1) = s1.Range("A" & i)
            s2.Range("B" & lrS + 1) = s1.Range("C" & i)
            s2.Range("C" & lrS + 1) = s1.Range("D" & i) & "-DA3"
            s2.Range("D" & lrS + 1) = s1.Range("I" & i) - s1.Range("N" & i)
            s2.Range("E" & lrS + 1) = s1.Range("G" & i)
            s2.Range("A" & lrS + 2) = s1.Range("A" & i)
            s2.Range("B" & lrS + 2) = s1.Range("C" & i)
            s2.Range("C" & lrS + 2) = s1.Range("D" & i) & "-NC3"
            s2.Range("D" & lrS + 2) = s1.Range("K" & i) - s1.Range("M" & i)
            s2.Range("E" & lrS + 2) = s1.Range("G" & i)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub
 
Thank You Alan sir, calculation is perfectly working fine .
Can your code combine with my old code so I can get Result like enclosed File

Thanks
 

Attachments

  • EXP File.xlsm
    962.9 KB · Views: 1
Why don't you just place a call to the new macro where you wish it to divert. Would that work for you? Or just insert it wherever you wish removing the Sub Name and Sub End.
 
Hi Sir,

applied your vba code and add this code in my old code ,calculation is perfectly fine . but in Model C400 DA3 and NC3 line item Reflecting 2 times.
I need if Model is C400 then DA3 and NC3 showing One time according to calculation.

thanks
 

Attachments

  • EXMP 2.xlsx
    951.6 KB · Views: 1
You have attached an .xlsx file without code attached. How am I to test and understand your code if you don't put it in a file? We are not mind readers here.
 
I am not understanding your issue. Please explain in detail using the example provided. No two lines in SD appear to be duplicated. Please explain step by step what you wish to happen. Explain in sufficient detail. Perhaps you need to create a mapping that explains what is to happen because as I understand what you asked for, I have given you exactly. So either I am not understanding you correctly or you are not explaining in clear enough terms.
 
I have enclosed file with required output sheet.
current output is in SD sheet . I need result like in REQ output sheet
 

Attachments

  • Copy of EXMP with VBA.xlsm
    968.4 KB · Views: 1
In my earlier post, I asked you to explain step by step what you wanted and you posted another file that you want me to figure out. Well, no. That's not how it works. If you are unwilling to answer my questions, step by step what you want, then I am unwilling to decipher your worksheet. I am volunteer with a life that extends beyond this forum and I don't have time for your laziness. If you truly want help, then do what is asked, don't presume anything else is an acceptable answer.
 
You must log in or register to reply here.
Back
Top