MAcro vba Code help

[ananya11]

HI all,

I am using one macro file with below code . it`s perfectly file 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 ,

example file enclosed SD worksheet is the required result sheet .





VBA code is :

Sub MyMacro()
Dim intCount As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngTRow, lngRow As Long
Set ws1 = Sheets("Raw Data")
Set ws2 = Sheets("SD")
 
'TargetDataRow
lngTRow = 8
ws1.Range("A1:B5").Copy
ws2.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ws2.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
  
ws2.Range("A7:E7") = Array("Customer Name", "S ID", "Device ID", "Counter", "Date")
  
For lngRow = 8 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
If Not ws1.Range("G" & lngRow).Value = "N/A" Then
  ws2.Range("A" & lngTRow).Value = ws1.Range("A" & lngRow).Value
  ws2.Range("B" & lngTRow).Value = ws1.Range("C" & lngRow).Value
  ws2.Range("E" & lngTRow).Value = ws1.Range("G" & lngRow).Value
  ws2.Range("E" & lngTRow).NumberFormat = "dd-mm-yy"
  For intCount = 1 To 8
    ws2.Range("A" & lngTRow + intCount).Value = ws2.Range("A" & lngTRow).Value
    ws2.Range("B" & lngTRow + intCount).Value = ws2.Range("B" & lngTRow).Value
    ws2.Range("E" & lngTRow + intCount).Value = ws2.Range("E" & lngTRow).Value
    ws2.Range("E" & lngTRow + intCount).NumberFormat = "dd-mm-yy"
  Next intCount
  ws2.Range("D" & lngTRow).Resize(7) = Application.Transpose(ws1.Range("H" & _
    lngRow).Resize(, 7).Value)
  For intCount = 0 To 4
    If ws2.Range("D" & lngTRow + intCount).Text = "N/A" Then
    ws2.Range("D" & lngTRow + intCount).Value = 0
    End If
  Next intCount
  ws2.Range("D" & lngTRow + 7).FormulaR1C1 = "=SUM(R[-6]C:R[-5]C)"
  ws2.Range("D" & lngTRow + 8).FormulaR1C1 = "=SUM(R[-4]C:R[-5]C)"
  ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
  ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-DA3"
  ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-DA4"
  ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-NC3"
  ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-NC4"
  ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-MC"
  ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-BC"
  ws2.Range("C" & lngTRow + 7) = ws1.Range("D" & lngRow) & "-B"
  ws2.Range("C" & lngTRow + 8) = ws1.Range("D" & lngRow) & "-C"
lngTRow = lngTRow + 9
End If
Next
End Sub

 

[Tinbendr]

For instance:

For lngRow = 8 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
If Not ws1.Range("G" & lngRow).Value = "N/A" Then
    Select Case UCase(ws1.Range("E" & lngRow))
        Case "C400"
            'Code here
        Case "N9000"
            'Code here
        Case "Q10000"
            'Code here
    End Select
    End If
Next

 

[ananya11]

HI Thanks for Reply ,

This is data in my raw Sheet .

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

adasd	sadaasd
sdsd	sdsd
dsf	sdf
dsf	954
afds	dsf
							TOTAL	DA3	DA4	NC3	NC4	MC	BC
Customer Name	ID	S ID	Device ID	P Name	Installation Date (dd-mm-yyyy)	Date and Time Last Received	11	12	13	112	113	172	173
abc	1323	as239876	as239876	asdertfgyu C123 liffdgg	02-24-2017	09-21-2017 02:01 PM	31537	2082	1876	13856	13723	1826	40
xyz	4343	uy567987	uy567987	asdertfgyu C400 liffdgg	09-16-2014	09-21-2017 11:30 AM	31537	2082	1876	13856	13723	1826	40

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col span="7"></colgroup>

 

[ananya11]

HI

And After Running Macro code Result Sheet SD like

adasd	sadaasd
sdsd	sdsd
dsf	sdf
dsf	954
afds	dsf
Customer Name	S ID	Device ID	Counter	Date
abc	as239876	as239876	31537	21-09-17
abc	as239876	as239876-DA3	2082	21-09-17
abc	as239876	as239876-DA4	1876	21-09-17
abc	as239876	as239876-NC3	13856	21-09-17
abc	as239876	as239876-NC4	13723	21-09-17
abc	as239876	as239876-MC	1826	21-09-17
abc	as239876	as239876-BC	40	21-09-17
abc	as239876	as239876-B	3958	21-09-17
abc	as239876	as239876-C	27579	21-09-17
xyz	uy567987	uy567987	31537	21-09-17
xyz	uy567987	uy567987-DA3	2042	21-09-17
xyz	uy567987	uy567987-DA4	1876	21-09-17
xyz	uy567987	uy567987-NC3	12030	21-09-17
xyz	uy567987	uy567987-NC4	13723	21-09-17
xyz	uy567987	uy567987-MC	1826	21-09-17
xyz	uy567987	uy567987-BC	40	21-09-17
xyz	uy567987	uy567987-B	3918	21-09-17
xyz	uy567987	uy567987-C	25753	21-09-17

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Thanks

Anu