Hi,
I've modified the code to suit the needs. It has been worked upon in Excel
2007 i unfortunately do not have excel 2010.
The code has the following assumptions
1. All serial numbers are in number format. The error message you were
getting earlier is possibly because a serial number was a number stored in
text format. You can be sure of this by checking if excel shows a green
pointer next to the cell.
Additionally I'm not sure if some of the VBA functions in 2007 would work
on 2010. I tried to make the code universal so give it a try.
Sub Serial()
Dim i As Long, count As Long, j As Long, Diff As Long
For i = 1 To 10000
If Cells(i, 1) <> "" And Cells(i + 1, 1) <> "" Then
Diff = Cells(i + 1, 1) - Cells(i, 1)
If Diff > 1 Then
For j = 1 To (Diff - 1)
Rows(i + 1 & ":" & i + 1).Select
Selection.Insert Shift:=xlDown
Rows(i & ":" & i).Select
Selection.Copy
Cells(i + 1, 1).Select
ActiveCell.PasteSpecial xlPasteAll
Cells(i + 1, 1) = Cells(i + 2, 1) - 1
Next j
i = i + (Diff - 1)
End If
Else
Exit For
End If
Next i
End Sub
Regards,
Damimkader
On Thursday, March 22, 2012 4:37:19 PM UTC+5:30, sandeep chhajer wrote:
>
> Dear Damimkader S. Meeran,
>
> Thanks for your reply.
> With ref to your question:
>
> I use office 2010 version.
>
> And I want to insert 3 column in between (I.e 4,5,6).
>
> Sandeep Chhajer.
>
>
> Sent on my BlackBerry® from Vodafone
> ------------------------------
> *From: * "Damimkader S. Meeran" <damim.ka...@gmail.com>
> *Sender: * excel-macros@googlegroups.com
> *Date: *Thu, 22 Mar 2012 03:57:16 -0700 (PDT)
> *To: *<excel-macros@googlegroups.com>
> *ReplyTo: * excel-macros@googlegroups.com
> *Subject: *$$Excel-Macros$$ Re: Macro for Insert a line for missing
> serial numbers.
>
> Hi Sandeep,
>
> Before I proceed to modify the code, could you let me know the following
>
> 1. Which version of excel are you using
> 2. Do you want the code to insert the rows equivalent to the missing
> serial numbers for e.g. if after number 3 you have 7, do you want the code
> to insert 4 rows in the middle of 3 and 7.
>
> Regards,
> Damimkader S. Meeran
>
> On Thursday, March 22, 2012 1:50:18 PM UTC+5:30, sandeep chhajer wrote:
>>
>> Dear Damimkader,
>>
>> while I am running this code I am getting the follwing error code
>> run time error 13
>>
>> type mismatch
>>
>> please help.
>>
>>
>> My problem has been reproduced
>>
>> My current Data is like this Sr. No. S_DISNO S_DISC S_UNIT S_SELP
>> Purchse
>> quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK
>> DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6
>> m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00
>> 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00
>>
>>
>> I want my data as follows
>>
>> Sr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK
>> DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00
>> 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00
>> 495064 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495065
>> 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495066 351 SILK
>> DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI
>>
>> 8 cu 3.6 m 2745.00 3.00 495068 351 SILK DOTHI 8 cu 3.6 m
>> 2745.00 3.00 495069 351 SILK DOTHI 8 cu 3.6 m 2745.00
>> 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00
>>
>>
>>
>> Copy the previous line only sr no has chamged by adding 1
>>
>>
>>
>>
>>
>> On 22 March 2012 02:26, Damimkader S. Meeran <damim.ka...@gmail.com>wrote:
>>
>>> Hi Sandeep,
>>>
>>> My code below assumes the following
>>>
>>> 1. The serial numbers are in column A
>>> 2. The Excel version you are using is 2007.
>>> 3. The code is good for 10,000 rows. Please modify if required.
>>>
>>> Sub Serial()
>>> Dim i As Long
>>> For i = 1 To 10000
>>> If Cells(i, 1) <> "" And Cells(i + 1, 1) <> "" Then
>>> If Cells(i + 1, 1) - Cells(i, 1) > 1 Then
>>> Rows(i + 1 & ":" & i + 1).Select
>>> Selection.Insert Shift:=xlDown,
>>> CopyOrigin:=xlFormatFromLeftOrAbove
>>> i = i + 1
>>> End If
>>> Else
>>> Exit For
>>> End If
>>> Next i
>>> End Sub
>>>
>>>
>>>
>>> On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote:
>>>>
>>>> Dear all,
>>>>
>>>> Please find my attachment ( I know asking question in this way is a
>>>> violation of our forum rule but sorry I don't have data card to copy the
>>>> queries from excel)
>>>>
>>>> Thanking you in advance.
>>>>
>>>> Sandeep Chhajer.
>>>>
>>>>
>>> On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote:
>>>>
>>>> Dear all,
>>>>
>>>> Please find my attachment ( I know asking question in this way is a
>>>> violation of our forum rule but sorry I don't have data card to copy the
>>>> queries from excel)
>>>>
>>>> Thanking you in advance.
>>>>
>>>> Sandeep Chhajer.
>>>>
>>>>
>>
>>
>> --
>> Regards,
>> Sandeep Kumar Chhajer.
>>
>> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
