Microsoft Excel - excel macro error help

 hi

 i need help in macro ;i have  one data in sheet1,like 

 

Customer Name	RDS ID	Device ID	Product Name	Installation Date (dd-mm-yyyy)	Date and Time Last Received	Total	BA3	BA4	CA3	CA4	B	C
ABV	MSR01437	MSR01437	iR2230	22-04-2026	31-12-2025 12:16	516299	5221	511078	0	0	516299	0
TYB	DCK03421	DCK03421	iR C2550i	22-04-2026	31-12-2025 03:58	103425	9680	84561	4115	5069	94241	9184
SBBLJ	KUG00135	KUG00135	imagePRESS C6000	2/7/2026	24-12-2025 11:36	334712	18846	347	308085	7434	19193	315519
JBDFLKS	DCK02100	DCK02100	iR C2550i	31-08-2025	31-12-2025 08:16	89806	745	76414	399	12248	77159	12647
NP]PR	MMU00941	MMU00941	iR C3380	20-10-2025	31-12-2025 07:12	N/A	N/A	N/A	N/A	N/A	N/A	N/A
RUIOTOR	DCK01854	DCK01854	iR C2550i	24-11-2025	N/A	0	0	0	0	0	0	0

i want above data in sheet 2 like below

Customer Name	RDS ID	Device ID	Counter	Date
ABILITIES INDIA PISTONS AND RINGS LTD.	MSR01437	MSR01437	516299	31-12-10
ABILITIES INDIA PISTONS AND RINGS LTD.	MSR01437	MSR01437-BA3	5221	31-12-10
ABILITIES INDIA PISTONS AND RINGS LTD.	MSR01437	MSR01437-BA4	511078	31-12-10
ABILITIES INDIA PISTONS AND RINGS LTD.	MSR01437	MSR01437-CA3	0	31-12-10
ABILITIES INDIA PISTONS AND RINGS LTD.	MSR01437	MSR01437-CA4	0	31-12-10
ABILITIES INDIA PISTONS AND RINGS LTD.	MSR01437	MSR01437-B	516299	31-12-10
ABILITIES INDIA PISTONS AND RINGS LTD.	MSR01437	MSR01437-C	0	31-12-10

i have one macro

Sub ConvertData()
   
Dim lngRow As Long, ws As Worksheet, lngValCols As Long, lngTRow As Long
Dim lngStartCol As Long, lngCol As Long
   
lngStartCol = 7 'Column number where the values starts
   
lngValCols = 7 'Number of columns with values
lngTRow = 2 'Target start row
   
Set ws = Worksheets("Sheet2") 'Target sheet
ws.Range("A1:E1") = Array("Customer Name", "RDS ID", "Device ID", "Counter", "Date")
   
For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
ws.Range("A" & lngTRow).Resize(lngValCols) = Range("A" & lngRow)
ws.Range("B" & lngTRow).Resize(lngValCols) = Range("B" & lngRow)
 
If IsDate(Range("F" & lngRow)) Then
ws.Range("E" & lngTRow).Resize(lngValCols) = Int(CDate(Range("F" & lngRow)))
Else
ws.Range("E" & lngTRow).Resize(lngValCols) = Range("F" & lngRow)
End If
  
For lngCol = lngStartCol To (lngStartCol + lngValCols) - 1
  If lngCol = lngStartCol Then
  ws.Range("C" & lngTRow) = Range("C" & lngRow)
  Else
  ws.Range("C" & lngTRow) = Range("C" & lngRow) & "-" & Cells(1, lngCol)
  End If
  ws.Range("D" & lngTRow) = Cells(lngRow, lngCol)
  lngTRow = lngTRow + 1
Next
Next
   
'Format ColE as date
ws.Range("E1").Resize(lngTRow).NumberFormat = "[$-409]d-mmm-yy;@"
   
End Sub

but i wants if N\A is in Date and Time Last Received column delete this perticuler whole row, and if N\A in other cell convert N\a to 0


Thanks

Anu

Hello Anu,
Try replacing the relevant part of your macro with the following.  The changes have been made in bold.  I have not tried it on your data.  This is also based on the N/A arising from an Excel Function (ie #N/A).  If this is not the case then the logical tests will be different and will have to check for the text values instead.

For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
  If Not Application.WorksheetFunction.IsNA(Range("F" & lngRow)) Then
    ws.Range("A" & lngTRow).Resize(lngValCols) = Range("A" & lngRow)
    ws.Range("B" & lngTRow).Resize(lngValCols) = Range("B" & lngRow)
 
    If IsDate(Range("F" & lngRow)) Then
    ws.Range("E" & lngTRow).Resize(lngValCols) = Int(CDate(Range("F" & lngRow)))
    Else
    ws.Range("E" & lngTRow).Resize(lngValCols) = Range("F" & lngRow)
    End If
 
    For lngCol = lngStartCol To (lngStartCol + lngValCols) - 1
    If lngCol = lngStartCol Then
    ws.Range("C" & lngTRow) = Range("C" & lngRow)
    Else
   ws.Range("C" & lngTRow) = Range("C" & lngRow) & "-" & Cells(1, lngCol)
    End If
    If Application.WorksheetFunction.IsNA(Cells(lngRow, lngCol)) Then
   ws.Range("D" & lngTRow) = 0
    Else
    ws.Range("D" & lngTRow) = Cells(lngRow, lngCol)
    End If
    lngTRow = lngTRow + 1
   Next
  End If
Next

Regards
Harry

thank u

but my corrct macro is below can you edit on acording this

Sub MyMacro()
 
Dim ws1 As Worksheet, ws2 As Worksheet
 
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
 
'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", "RDS ID", "Device ID", "Counter", "Date")
 
For lngRow = 8 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws2.Range("A" & lngTRow).Resize(7) = ws1.Range("A" & lngRow)
ws2.Range("B" & lngTRow).Resize(7) = ws1.Range("C" & lngRow)
ws2.Range("E" & lngTRow).Resize(7) = Int(ws1.Range("G" & lngRow).Value)
ws2.Range("E" & lngTRow).Resize(7).NumberFormat = "dd-mm-yy"
ws2.Range("D" & lngTRow).Resize(5) = Application.Transpose(ws1.Range("H" & _
      lngRow).Resize(, 5).Value)
ws2.Range("D" & lngTRow + 5).FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"
ws2.Range("D" & lngTRow + 6).FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-BA3"
ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-BA4"
ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-CA3"
ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-CA4"
ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-B"
ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-C"
lngTRow = lngTRow + 7

Next

End Sub

Hello Usha,
Try this.  I have assumed that your N/A is entered as text.  If this is not the case revert to the format that I showed previously. Also I assumed that, if the first counter data is unavailable, all will be unavailable.  If that is not the case then you would have to step through the cells one by one and convert each separately.  If the one-in-all-in case applies, you might also have to enter an array of zeroes [ Array(0, 0, 0, 0, 0) ] rather than the single one that I have put in and if you do you might also have to apply the transpose.

Once again, I have not tested this with your data.

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).Resize(7) = ws1.Range("A" & lngRow)
  ws2.Range("B" & lngTRow).Resize(7) = ws1.Range("C" & lngRow)
  ws2.Range("E" & lngTRow).Resize(7) = Int(ws1.Range("G" & lngRow).Value)
  ws2.Range("E" & lngTRow).Resize(7).NumberFormat = "dd-mm-yy"
  If ws1.Range(“H” & lngRow).Value = “N/A” Then
   ws2.Range("D" & lngTRow).Resize(5) = 0
  Else
    ws2.Range("D" & lngTRow).Resize(5) = Application.Transpose(ws1.Range("H" & _
    lngRow).Resize(, 5).Value)
  End If
  ws2.Range("D" & lngTRow + 5).FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"
  ws2.Range("D" & lngTRow + 6).FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
  ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
  ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-BA3"
  ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-BA4"
  ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-CA3"
  ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-CA4"
  ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-B"
  ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-C"
End If
lngTRow = lngTRow + 7
Next

Regards
Harry

HI it`s working but when i have rum this macro if in sheet 1 column h is N\a then in sheet 2 row 8,9,10,11,12,13,14 atomactly show 0, in corrct manner it sould be if in sheet 1 h column value should be in row 8, i -9 j- ect ..

like sheet 1 carry

Customer Name	Customer ID	RDS ID	Device ID	Product Name	Installation Date	Date and Time Last Received	101	112	113	122	123
Ac NeiLsen	62123	CJQ00257	CJQ00257	iR5055	6/24/2011	8/24/2012 9:00	N/A	36925	2172125	N/A	N/A
Ac NeiLsen	62123	CYC01683	CYC01683	iR5055	6/24/2011	8/23/2012 19:16	660365	20931	639434	N/A	N/A
Ac NeiLsen	62123	ETG10759	ETG10759	iR-ADV C5030	6/24/2011	4/30/2012 16:46	77634	193	11753	1049	64639
ACTIVAIR AIRFREIGHT IND. PVT. LTD.	24723	KNR02115	KNR02115	iR C2570i	9/1/2026	12/26/2012 9:59	537811	28	534776	3	3004
AERO CLUB	25420	DGR03517	DGR03517	iR3235	3/21/2012	1/23/2013 10:09	93567	1715	91852	0	0

after rum this macro

Customer Name	RDS ID	Device ID	Counter	Date
Ac NeiLsen	CJQ00257	CJQ00257	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA3	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA4	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA3	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA4	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-B	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-C	0	24-08-12

 

BUt i need

Customer Name	RDS ID	Device ID	Counter	Date
Ac NeiLsen	CJQ00257	CJQ00257	2209050	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA3	36925	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA4	2172125	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA3	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA4	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-B	2209050	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-C	0	24-08-12

hi
 thnk u i want only in my sheet 1 like this

Customer Name	Customer ID	RDS ID	Device ID	Product Name	Installation Date	Date and Time Last Received	101	112	113	122	123
Ac NeiLsen	62123	CJQ00257	CJQ00257	iR5055	6/24/2011	8/24/2012 9:00	2172125	N/A	2172125		N/A  N\A

now i wants rum my old macro iwant only if any N\A value in any cell should conver 0 only like below .

Customer Name	RDS ID	Device ID	Counter	Date
Ac NeiLsen	CJQ00257	CJQ00257	2172125	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA3	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA4	2172125	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA3	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA4	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-B	2172125	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-C	0	24-08-12

Hello Usha,
Once again not tested but try this.

Dim intCount As Integer
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).Resize(7) = ws1.Range("A" & lngRow)
  ws2.Range("B" & lngTRow).Resize(7) = ws1.Range("C" & lngRow)
  ws2.Range("E" & lngTRow).Resize(7) = Int(ws1.Range("G" & lngRow).Value)
  ws2.Range("E" & lngTRow).Resize(7).NumberFormat = "dd-mm-yy"
  ws2.Range("D" & lngTRow).Resize(5) = Application.Transpose(ws1.Range("H" & _
    lngRow).Resize(, 5).Value)
  For intCount = 0 to 4
    If ws2.Range(“D” & lngTRow + intCount).Value = “N/A” Then
    ws2.Range(“D” & lngTRow + intCount).Value = 0
    End If
  Next intCount
  ws2.Range("D" & lngTRow + 5).FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"
  ws2.Range("D" & lngTRow + 6).FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
  ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
  ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-BA3"
  ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-BA4"
  ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-CA3"
  ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-CA4"
  ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-B"
  ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-C"
End If
lngTRow = lngTRow + 7
Next

Regards
Harry

hi thans

but error Can`t execute code in break mode

If Not ws1.Range("G" & lngRow).Value = “N / A” Then

And also now when

Device ID is showing blank Customer Name RDS ID Device ID Counter Date Ac NeiLsen CJQ00257 2172125 24-08-12 Ac NeiLsen CJQ00257 N/A 24-08-12 Ac NeiLsen CJQ00257 2172125 24-08-12 Ac NeiLsen CJQ00257 N/A 24-08-12 Ac NeiLsen CJQ00257 N/A 24-08-12 Ac NeiLsen CJQ00257 24-08-12 Ac NeiLsen CJQ00257 24-08-12  thanks anu

hi harry

is any  option in conditional formatting where we salect the D column and put the condition is in D column N\A convert to 0
 thanks

Hello Usha,
If a macro is stopped or fails the code needs to be reset by clicking on the button with the blue square (next to the run button) in the VBA editor.
Try this code
Option Explicit

Sub MyMacro()
Dim intCount As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngTRow, lngRow As Long

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
 
'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", "RDS ID", "Device ID", "Counter", "Date")
 
For lngRow = 2 To 2 + ws1.Cells(Rows.Count, "A").End(xlUp).Row - 2
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"
  ws2.Range("D" & lngTRow).Resize(5) = Application.Transpose(ws1.Range("H" & _
    lngRow).Resize(, 5).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 + 5).FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"
  ws2.Range("D" & lngTRow + 6).FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
  ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
  ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-BA3"
  ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-BA4"
  ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-CA3"
  ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-CA4"
  ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-B"
  ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-C"
End If
lngTRow = lngTRow + 7
Next
End Sub


Regards
Harry

Hello Usha,
RE conditional formatting - all you could do would be to set the font colour to none if the cell is N/A.  However if it is one of the cells that you are trying to add with the formulae that you insert then your sum will not work.
Please try my latest code. 
Regards
Harry

hello harry,

and thank you very much for yor support it`s nice now all is working littel thing i want in sheet 2 i want like below.

Company Name:	CIPL OIS Delhi(ASN105)
Report Date and Time:	01-23-2013 11:48 AM(+05:30)
Customer Name:	All
Total Number of Registered RDSs:	375
Total Number of Registered Devices:	375
Customer Name	RDS ID	Device ID	Counter	Date
Ac NeiLsen	CJQ00257	CJQ00257	2209050	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA3	36925	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA4	2172125	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA3	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA4	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-B	2209050	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-C	0	24-08-12
Ac NeiLsen	CYC01683	CYC01683	660365	23-08-12

 right now when i have run this macro it shows

Company Name:	CIPL OIS Delhi(ASN105)
Report Date and Time:	01-23-2013 11:48 AM(+05:30)
Customer Name:	All
Total Number of Registered RDSs:	375
Total Number of Registered Devices:	375
Customer Name	RDS ID	Device ID	Counter	Date
Report Date and Time:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Customer Name:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Total Number of Registered RDSs:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Total Number of Registered Devices:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Customer Name	RDS ID	Device ID	101	Date and Time Last Received
	RDS ID	Device ID-BA3	112
	RDS ID	Device ID-BA4	113
	RDS ID	Device ID-CA3	122
	RDS ID	Device ID-CA4	123
	RDS ID	Device ID-B	225
	RDS ID	Device ID-C	245
Ac NeiLsen	CJQ00257	CJQ00257	2209050	24-08-12
		CJQ00257-BA3	36925
		CJQ00257-BA4	2172125
		CJQ00257-CA3	0
		CJQ00257-CA4	0
		CJQ00257-B	2209050
		CJQ00257-C	0

thanks

anu

hello harry,

and thank you very much for yor support it`s nice now all is working littel thing i want in sheet 2 i want like below.

Company Name:	CIPL OIS Delhi(ASN105)
Report Date and Time:	01-23-2013 11:48 AM(+05:30)
Customer Name:	All
Total Number of Registered RDSs:	375
Total Number of Registered Devices:	375
Customer Name	RDS ID	Device ID	Counter	Date
Ac NeiLsen	CJQ00257	CJQ00257	2209050	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA3	36925	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-BA4	2172125	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA3	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-CA4	0	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-B	2209050	24-08-12
Ac NeiLsen	CJQ00257	CJQ00257-C	0	24-08-12
Ac NeiLsen	CYC01683	CYC01683	660365	23-08-12

 right now when i have run this macro it shows

Company Name:	CIPL OIS Delhi(ASN105)
Report Date and Time:	01-23-2013 11:48 AM(+05:30)
Customer Name:	All
Total Number of Registered RDSs:	375
Total Number of Registered Devices:	375
Customer Name	RDS ID	Device ID	Counter	Date
Report Date and Time:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Customer Name:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Total Number of Registered RDSs:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Total Number of Registered Devices:
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
		-BA3
		-BA4
		-CA3
		-CA4
		-B	0
		-C	0
Customer Name	RDS ID	Device ID	101	Date and Time Last Received
	RDS ID	Device ID-BA3	112
	RDS ID	Device ID-BA4	113
	RDS ID	Device ID-CA3	122
	RDS ID	Device ID-CA4	123
	RDS ID	Device ID-B	225
	RDS ID	Device ID-C	245
Ac NeiLsen	CJQ00257	CJQ00257	2209050	24-08-12
		CJQ00257-BA3	36925
		CJQ00257-BA4	2172125
		CJQ00257-CA3	0
		CJQ00257-CA4	0
		CJQ00257-B	2209050
		CJQ00257-C	0

thanks

anu

Hello Usha,
When I run the macro using the data that you published, this is the output that I get.

Fairly obviously there is some significant difference between the data that you have in your spreadsheet and the data that you published.
It is quite difficult to be helpful when the goalposts keep moving.
Regards
Harry

hi harry but earlier when i m uesing macro i am getting same thing . my earlier macro was

Sub MyMacro()
 
Dim ws1 As Worksheet, ws2 As Worksheet
 
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
 
'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", "RDS ID", "Device ID", "Counter", "Date")
 
For lngRow = 8 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws2.Range("A" & lngTRow).Resize(7) = ws1.Range("A" & lngRow)
ws2.Range("B" & lngTRow).Resize(7) = ws1.Range("C" & lngRow)
ws2.Range("E" & lngTRow).Resize(7) = Int(ws1.Range("G" & lngRow).Value)
ws2.Range("E" & lngTRow).Resize(7).NumberFormat = "dd-mm-yy"
ws2.Range("D" & lngTRow).Resize(5) = Application.Transpose(ws1.Range("H" & _
    lngRow).Resize(, 5).Value)
ws2.Range("D" & lngTRow + 5).FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"
ws2.Range("D" & lngTRow + 6).FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-BA3"
ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-BA4"
ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-CA3"
ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-CA4"
ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-B"
ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-C"
lngTRow = lngTRow + 7

Next

End Sub

Hello Usha,

usha_1.zip

Here is my file with the macro.
Harry

hellow harry

 my base data is like below in sheet 1

Company Name:	eiryeiwornwek
Report Date and Time:	01-23-2013 11:48 AM(+05:30)
Customer Name:	All
Total Number	375
Total Number	375
Customer Name	Customer ID	RDS ID	Device ID	Product Name	Installation Date	Date and Time Last Received	101	112	113	122	123
abc	45632	7779897	7779897	454	06/24/11	08/24/12 9:00	2209050	36925	2172125	N/A	N/A
abc	45632	121311	121311	121	06/24/11	08/23/12 19:16	660365	20931	639434	N/A	N/A
abc	45632	7897989	7897989	121	06/24/11	04/30/12 16:46	77634	193	11753	1049	64639

Hi Usha,
I thought something like that might have been happening but it wasn't immediately obvious from what you gave as example.
Try this.

Option Explicit

Sub MyMacro()
Dim intCount As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngTRow, lngRow As Long

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
 
'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", "RDS 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 6
    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(5) = Application.Transpose(ws1.Range("H" & _
    lngRow).Resize(, 5).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 + 5).FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"
  ws2.Range("D" & lngTRow + 6).FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
  ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
  ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-BA3"
  ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-BA4"
  ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-CA3"
  ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-CA4"
  ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-B"
  ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-C"
End If
lngTRow = lngTRow + 7
Next
End Sub


I have added code to copy the company name, productID and date as well to comply with your requirements.
Regards
Harry

thank u so much it`s working nice

hi harry,

macro working fine, but in sheet 2 some rows shows blank, may be its thoes which are carry N\A in date colum, is ther option in sheet 2 no row blank, right now between sheet 2 data some rows blank

thanks
 anu

Hi Usha
Try:
Option Explicit

Sub MyMacro()
Dim intCount As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngTRow, lngRow As Long

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
 
'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", "RDS 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 6
    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(5) = Application.Transpose(ws1.Range("H" & _
    lngRow).Resize(, 5).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 + 5).FormulaR1C1 = "=SUM(R[-4]C:R[-3]C)"
  ws2.Range("D" & lngTRow + 6).FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
  ws2.Range("C" & lngTRow) = ws1.Range("D" & lngRow)
  ws2.Range("C" & lngTRow + 1) = ws1.Range("D" & lngRow) & "-BA3"
  ws2.Range("C" & lngTRow + 2) = ws1.Range("D" & lngRow) & "-BA4"
  ws2.Range("C" & lngTRow + 3) = ws1.Range("D" & lngRow) & "-CA3"
  ws2.Range("C" & lngTRow + 4) = ws1.Range("D" & lngRow) & "-CA4"
  ws2.Range("C" & lngTRow + 5) = ws1.Range("D" & lngRow) & "-B"
  ws2.Range("C" & lngTRow + 6) = ws1.Range("D" & lngRow) & "-C"
lngTRow = lngTRow + 7
End If
Next
End Sub


Harry

thank you so much now it`s perfectly working fine

thanks
anu