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 :
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 :
Code:
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
Last edited by a moderator:
