• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro coding need for arranging a code

P

pramoth.u

New Member
Friendz...Good morning to all


I need a macro coding for the below requirement.


I have a entries in a continuous rows in sheet1 as like below

S.no IP Code1 Code2

1 221.135.254.168 edit "221.135.254.168"         set subnet 221.135.254.168

2 221.135.254.169 edit "221.135.254.169" set subnet 221.135.254.169

3 221.135.254.170 edit "221.135.254.170" set subnet 221.135.254.170


I wanted the above entries to be written in sheet2 as follows

S.no Column1 Column2

1 221.135.254.168 edit "221.135.254.168"

set subnet 221.135.254.168

2 221.135.254.169 edit "221.135.254.169"

set subnet 221.135.254.169

3 221.135.254.170 edit "221.135.254.170"

set subnet 221.135.254.170

Can anyone help me out with a macro code to fulfill my requirement.
 
Hi Pramoth ,


Can you confirm the following ?


The following entry is one continuous piece of text in one cell :


1 221.135.254.168 edit "221.135.254.168" set subnet 221.135.254.168


You want this parsed into the 4 individual components.


Is this right ?


Narayan
 
Hi Narayan,


Entries is in 3 different columns Starts from A2 to C2 in sheet1.


I want that entires in sheet 2 as

In A2 column of sheet 2 should contain 221.135.254.168 subsequently in B2 column -edit "221.135.254.168",in b3 column-set subnet 221.135.254.168

Then next 221.135.254.169 in A4 and others texts in B4 & B5 and so on.


Hope am clear with my requirement.
 
Hi Pramoth ,


Thanks for the clarification. I have one doubt , still.


When you say the data is in 3 columns , is it like this ?


A2 : Sl. No.


B2 : IP (221.135.254.168)


C2 : Code1 ( edit "221.135.254.168" set subnet 221.135.254.168 )


Or is the text edit "221.135.254.168" in C2 , and the text "set subnet 221.135.254.168" in cell D2 , so that there are totally 4 columns ?


Narayan
 
Hi Pramoth ,


Can you copy the following into your code for ThisWorkBook , and see if it is OK ?


Private Sub Copy_text()

Application.ScreenUpdating = False

Start_Range = "A2" ' Change this address to whatever is your requirement

End_Range = "D4" ' Change this address to whatever is your requirement

ThisWorkbook.Worksheets("Sheet1").Activate

Range(Start_Range, End_Range).Select

Number_of_Rows = Selection.Rows.Count

Entry_Count = 0

For Entry_Count = 0 To Number_of_Rows

Serial_Num = Range(Start_Range).Offset(Entry_Count, 0).Value

IP_Code = Range(Start_Range).Offset(Entry_Count, 1).Value

Code1_Text = Range(Start_Range).Offset(Entry_Count, 2).Value

Code2_Text = Range(Start_Range).Offset(Entry_Count, 3).Value

ThisWorkbook.Worksheets("Sheet2").Activate

Range(Start_Range).Offset(Entry_Count * 2, 0).Value = Serial_Num

Range(Start_Range).Offset(Entry_Count * 2, 1).Value = IP_Code

Range(Start_Range).Offset(Entry_Count * 2, 2).Value = Code1_Text

Range(Start_Range).Offset(Entry_Count * 2 + 1, 0).Value = Code2_Text

ThisWorkbook.Worksheets("Sheet1").Activate

Next

Application.ScreenUpdating = True

End Sub


Narayan
 
Hi Narayan...VGM


I need to add up one more line along with this code...pls help i tried to do it myself to max but it's not working out.


Application.ScreenUpdating = False

Start_Range = "A2"

End_Range = "E501"

ThisWorkbook.Worksheets("Sheet1").Activate

Range(Start_Range, End_Range).Select

Number_of_Rows = Selection.Rows.Count

Entry_Count = 1

For Entry_Count = 0 To Number_of_Rows

Serial_Num = Range(Start_Range).Offset(Entry_Count, 0).Value

IP_Code = Range(Start_Range).Offset(Entry_Count, 1).Value

Code1_Text = Range(Start_Range).Offset(Entry_Count, 2).Value

Code2_Text = Range(Start_Range).Offset(Entry_Count, 3).Value

Code3_Text = Range(Start_Range).Offset(Entry_Count, 4).Value

ThisWorkbook.Worksheets("Sheet2").Activate

Range(Start_Range).Offset(Entry_Count * 2, 0).Value = Serial_Num

Range(Start_Range).Offset(Entry_Count * 2, 1).Value = IP_Code

Range(Start_Range).Offset(Entry_Count * 2, 2).Value = Code1_Text

Range(Start_Range).Offset(Entry_Count * 2 + 1, 2).Value = Code2_Text

Range(Start_Range).Offset(Entry_Count * 2 + 2, 2).Value = Code3_Text


I have added "Range(Start_Range).Offset(Entry_Count * 2 + 2, 2).Value = Code3_Text" this code.When i run the code the first output comes exactly as required however when i run the code continuously it overwrites the code3_Text.Please help me out on this.
 
Hi Pramoth ,


If I understand you correctly , you have added one more column in your raw data , which contains Code3.


Where do you want this to appear , in Sheet2 ? In column B ( in the second row ) or in the third row for each entry ?


You have typed in the following :


Range(Start_Range).Offset(Entry_Count * 2 + 1, 2).Value = Code2_Text

Range(Start_Range).Offset(Entry_Count * 2 + 2, 2).Value = Code3_Text


The first parameter for the Offset function is the row offset , and the second parameter is the column offset ; the value of Entry_Count * 2 + 1 and Entry_Count * 2 +2 is not right.


If you want each entry in the raw data to occupy 3 rows in Sheet2 , then the multiplier for Entry_Count should be 3.


Thus , the first entry will occupy rows 1 , 2 and 3 ; the second will occupy rows 4 , 5 and 6 ; the third will occupy rows 7 , 8 and 9 ,....


Change the above two statements to :


Range(Start_Range).Offset(Entry_Count * 3 + 1, 0).Value = Code2_Text

Range(Start_Range).Offset(Entry_Count * 3 + 2, 0).Value = Code3_Text


If you do this , the raw data will be split up as follows :


Serial_Num , IP_Code and Code1 in columns A , B and C , in the first row

Code2 in column A , in the second row

Code3 in column A , in the third row


This will repeat in the subsequent rows.


Instead , you can have the two statements as follows :


Range(Start_Range).Offset(Entry_Count * 2 + 1, 0).Value = Code2_Text

Range(Start_Range).Offset(Entry_Count * 2 + 1, 1).Value = Code3_Text


If you do this , the raw data will be split up as follows :


Serial_Num , IP_Code and Code1 in columns A , B and C , in the first row

Code2 in column A , in the second row

Code3 in column B , in the second row


This will repeat in the subsequent rows.


Just by the way , the statement Entry_Count = 1 before the For statement , is redundant ; since Entry_Count will anyway take its values from the For statement , the assignment statement is unnecessary. This is actually my mistake since my original post has it. Sorry.


Narayan
 
If I understand you correctly , you have added one more column in your raw data , which contains Code3.


Where do you want this to appear , in Sheet2 ? In column B ( in the second row ) or in the third row for each entry ?


I want to place code_Text1 to appear in column C2,Code_Text3 in C3 and Code_text3 in C4 in sheet2.


similarly the other entry ie 2nd Ip should start from cell a5 and related infos in other subsequent columns.


Hope iam clear narayan.
 
Hi Pramoth ,


There is still some confusion !


Originally , the way it was coded ,


Serial_Num , IP_Code and Code1_Text would appear in one row , in columnns A , B and C.


Code2_Text would appear in the next row , in column A.


Now , you want that the reorganization should result in the follows :


Serial_Num , IP_Code and Code1_Text would appear in one row , in columnns A , B and C.


Code2_Text should appear in the second row , in column C.


Code3_Text should appear in the third row , in column C.


If my understanding is correct , then the code should be as follows :


Private Sub Copy_text()

Application.ScreenUpdating = False

Start_Range = "A2"

End_Range = "E4"

ThisWorkbook.Worksheets("Sheet1").Activate

Range(Start_Range, End_Range).Select

Number_of_Rows = Selection.Rows.Count

Entry_Count = 0

For Entry_Count = 0 To Number_of_Rows

Serial_Num = Range(Start_Range).Offset(Entry_Count, 0).Value

IP_Code = Range(Start_Range).Offset(Entry_Count, 1).Value

Code1_Text = Range(Start_Range).Offset(Entry_Count, 2).Value

Code2_Text = Range(Start_Range).Offset(Entry_Count, 3).Value

Code3_Text = Range(Start_Range).Offset(Entry_Count, 4).Value

ThisWorkbook.Worksheets("Sheet2").Activate

Range(Start_Range).Offset(Entry_Count * 3, 0).Value = Serial_Num

Range(Start_Range).Offset(Entry_Count * 3, 1).Value = IP_Code

Range(Start_Range).Offset(Entry_Count * 3, 2).Value = Code1_Text

Range(Start_Range).Offset(Entry_Count * 3 + 1, 2).Value = Code2_Text

Range(Start_Range).Offset(Entry_Count * 3 + 2, 2).Value = Code3_Text

ThisWorkbook.Worksheets("Sheet1").Activate

Next

Application.ScreenUpdating = True

End Sub


Narayan
 
Hi Narayan,


Code is working fine now with some small modification.Now it fulfilled my requirement.


Please find the modified code below.


Application.ScreenUpdating = False

Start_Range = "A2"

End_Range = "E501"

ThisWorkbook.Worksheets("Sheet1").Activate

Range(Start_Range, End_Range).Select

Number_of_Rows = Selection.Rows.Count

Entry_Count = 1

For Entry_Count = 0 To Number_of_Rows

Serial_Num = Range(Start_Range).Offset(Entry_Count, 0).Value

IP_Code = Range(Start_Range).Offset(Entry_Count, 1).Value

Code1_Text = Range(Start_Range).Offset(Entry_Count, 2).Value

Code2_Text = Range(Start_Range).Offset(Entry_Count, 3).Value

Code3_Text = Range(Start_Range).Offset(Entry_Count, 4).Value

ThisWorkbook.Worksheets("Sheet2").Activate

Range(Start_Range).Offset(Entry_Count * 3, 0).Value = Serial_Num

Range(Start_Range).Offset(Entry_Count * 3, 1).Value = IP_Code

Range(Start_Range).Offset(Entry_Count * 3, 2).Value = Code1_Text

Range(Start_Range).Offset(Entry_Count * 3 + 1, 2).Value = Code2_Text

Range(Start_Range).Offset(Entry_Count * 3 + 2, 2).Value = Code3_Text

ThisWorkbook.Worksheets("Sheet1").Activate

Next

Application.ScreenUpdating = True

ThisWorkbook.Worksheets("Sheet2").Activate
 
You must log in or register to reply here.
Back
Top