Adeel
01-04-2020, 01:19 AM
Hi All
i need some improvement in code that code isn't working when i create header as per my need and when i remove all header code working fine!
2nd issue is this that there is total count of column A isn't equal to column C count so last WBK isn't generating with 100 numbers it should be generated with remaining numbers is whatever in count!
Sub test()
Dim a, b, i As Long, ii As Long, n As Long, t As Long, HDR As String, x
HDR = "Customer ID,Mobile Number,Customer Name,CNIC,Email Address,Package Plan,Bolton1," & _
"Bundles,Connection Status,Access Level,Credit Limit,Natureof Sales," & _
"Deposit Amount/Waiver Code,Special Line Level Info,Special Number Charge Type," & _
"Hand Set,Special Number Charges,ICCID,Verified,Comments,Sales Feedback,SPOCMSISDN,Sales ID"
a = [a2].CurrentRegion.Value
x = [index(b2:b1000&"-"&c2:c100&if(countifs(b2:b1000,b2:b1000,c2:c1000,c2:c1000)>1,"-"&countifs(offset(b2:b1000,,,row(1:1000)),b2:b1000,offset(c2:c1000,,,row(1:10 00)),c2:c1000),""),,)]
For i = 1 To UBound(a, 1)
If a(i, 3) = "" Then Exit For
ReDim b(1 To a(i, 3))
For ii = t + 1 To UBound(a, 1)
If a(i, 3) = "" Then Exit For
n = n + 1
b(n) = "," & Format$(Replace (Replace (a(ii, 1), "-", ""), " ", ""), _
String(9, "0")) & String(12, ",") & a(i, 4) & String(10, ",") & a(i, 2)
If n = a(i, 3) Then
Open ThisWorkbook.Path & "\" & x(i, 1) & ".csv" For Output As #1
Print #1, Join(Array(HDR, Join(b, vbCrLf)), vbCrLf);
Close #1
t = ii: n = 0: Exit For
End If
Next ii, i End Sub
i need some improvement in code that code isn't working when i create header as per my need and when i remove all header code working fine!
2nd issue is this that there is total count of column A isn't equal to column C count so last WBK isn't generating with 100 numbers it should be generated with remaining numbers is whatever in count!
Sub test()
Dim a, b, i As Long, ii As Long, n As Long, t As Long, HDR As String, x
HDR = "Customer ID,Mobile Number,Customer Name,CNIC,Email Address,Package Plan,Bolton1," & _
"Bundles,Connection Status,Access Level,Credit Limit,Natureof Sales," & _
"Deposit Amount/Waiver Code,Special Line Level Info,Special Number Charge Type," & _
"Hand Set,Special Number Charges,ICCID,Verified,Comments,Sales Feedback,SPOCMSISDN,Sales ID"
a = [a2].CurrentRegion.Value
x = [index(b2:b1000&"-"&c2:c100&if(countifs(b2:b1000,b2:b1000,c2:c1000,c2:c1000)>1,"-"&countifs(offset(b2:b1000,,,row(1:1000)),b2:b1000,offset(c2:c1000,,,row(1:10 00)),c2:c1000),""),,)]
For i = 1 To UBound(a, 1)
If a(i, 3) = "" Then Exit For
ReDim b(1 To a(i, 3))
For ii = t + 1 To UBound(a, 1)
If a(i, 3) = "" Then Exit For
n = n + 1
b(n) = "," & Format$(Replace (Replace (a(ii, 1), "-", ""), " ", ""), _
String(9, "0")) & String(12, ",") & a(i, 4) & String(10, ",") & a(i, 2)
If n = a(i, 3) Then
Open ThisWorkbook.Path & "\" & x(i, 1) & ".csv" For Output As #1
Print #1, Join(Array(HDR, Join(b, vbCrLf)), vbCrLf);
Close #1
t = ii: n = 0: Exit For
End If
Next ii, i End Sub