Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel sheet for extension no's

Posted on 2007-07-24
24
Medium Priority
?
418 Views
Last Modified: 2010-03-05
Hi,

I have a excel sheet which has this.

Sr.No      Service ID      Ext No      Building      Floor      Location of phone      Description


I want a way to make work easier.
1. Need a box where i can type the ext no and the macro takes the found data to a new sheet.This should be repeated until i click done button.
So that at the end i shall have all the ext found in a new sheet.

Regards
Sharath
0
Comment
Question by:bsharath
  • 12
  • 7
  • 4
23 Comments
 
LVL 17

Expert Comment

by:gtgloner
ID: 19557828
Here is a macro that should isolate the data series with the extensions you want (you might have to edit the code where it says "Sheet1" with the name of the sheet where your data is):

Sub extensions()

    Dim extension As String
    extension = InputBox("Please type the extension in the box below")

    Columns("A:I").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:=extension
    Selection.Copy
    Sheets("Sheet1").Select
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Selection.AutoFilter
   
End Sub
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 19557884
A couple of afterthoughts: this code names the newly created sheet with the extension you type in the input box. Also, this code assumes that your data is contained in columns A thru I and the titles are in row 1. If it is elsewhere on your data sheet, you must edit the code line:

Columns("A:I").Select

to whichever range your data lies in.



Sub extensions()

    Dim extension As String
    extension = InputBox("Please type the extension in the box below")

    Columns("A:I").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:=extension
    Selection.Copy
    Sheets("Sheet1").Select
    Sheets.Add
    ActiveSheet.Name = extension
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Selection.AutoFilter
   
End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19562591
I get this error.

Run time error '9':
Subscript out of range.

I have changed the range according to my excel and even changed the sheet name
After i run the macro a new sheet is created with the search ext no and then i get the above error.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Expert Comment

by:gtgloner
ID: 19564286
Could you post your workbook here for me to upload and I will adapt my macro to your data sheet
0
 
LVL 11

Author Comment

by:bsharath
ID: 19564481
I have this data in the excel.

Sr.No      Service ID      Ext No      Building      Floor      Location of phone      Description
1      4439818801      8801      GRP      GRP-1F-REC      GRP Reception      Location Found
2      4439818802      8802      GRP      GR-1F      GRP-1F-112      Location Found
3      4439818803      8803      GRP      GR-2F      GRP-2F-099      Location Found
4      4439818804      8804      GRP      GR-2F      GRP-2F-096      Location Found
5      4439818805      8805      GRP      GR-2F      GRP-2F-105      Location Found
6      4439818806      8806      GRP      GR-3F      GRP-3F-089      Location Found
7      4439818807      8807      GRP      GR-2F      GRP-2F-102      Location Found
8      4439818808      8808      GRP      GR-2F      GRP-2F-107      Location Found
9      4439818809      8809      GRP      GR-2F      GRP-2F-111      Location Found
10      4439818810      8810      GRP      GR-2F      GRP-2F-154      Location Found
11      4439818811      8811      GRP      GR-GF      GR-GF-083      Can be deactivated
12      4439818812      8812      GRP      GR-2F      GRP-2F-121      Deactivated, to be activated
13      4439818813      8813      GRP      GR-2F      GRP-2F-124      Location Found
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 19564578
It would be better if you could just post the workbook to EA Stuff. I would then fix it so it would be working the way you want and post it back here for you to upload.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19564595
Can i know the path to upload
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 19564749
I personally use this site here:

http://www.mytempdir.com/

You upload your file and a window pops up giving you a URL to post for someone else to access the file.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19565394
I have uploaded the file here

http://www.filepatio.com/2975
0
 
LVL 11

Author Comment

by:bsharath
ID: 19565410
Save target as.
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 19571116
The file you uploaded has an extension .xlsx which will not open in excel. Please send again and make sure the extension is .xls
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 19571413
OK, I don't have Excel 2007 so never saw that extension before. Here is a link to the file with a button that will execute a macro that worked on my computer:

http://www.mytempdir.com/1374577
0
 
LVL 11

Author Comment

by:bsharath
ID: 19571698
Thanks but when i search for a ext no its creating a new sheet for each search.Can it update in the same sheet.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19571709
If you can let the macro search for any text or numbers found and copy to new sheet that would be great

EX:
In the input box if i even type a ext or a Service ID,Floor,Location of phone
If all these can be put to a new sheet.

0
 
LVL 11

Author Comment

by:bsharath
ID: 19581603
Any help...
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19583987
Sub a1()
Dim str1, str2() as String
str1 = InputBox("SearchValue)

Dim valuefound as Boolean,s2row as Integer
s2row=1

For i=1 to Sheet1.Usedrange.Rows.Count
   valuefound = False
   For j=1 to Sheet1.Usedrange.Columns.Count
          if UCase(Sheet1.Cells(i,j)) = UCase(str1) then
               valuefound = True
               Exit For
           End if
  Next
   if valuefound = True then
        Sheet1.Rows(i).Copy Destination:= Sheet2.Rows(s2row)
        s2row = s2row + 1
  End if
Next

End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19597543
Hitesh i get this error.
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Syntax error
---------------------------
OK   Help  
---------------------------
0
 
LVL 11

Author Comment

by:bsharath
ID: 19597547
Hitesh
i think you left a "
str1 = InputBox("SearchValue)

Now i checked its coping the data correctly but its asking the box only 1 time.It should go on asking until i click cancel or esc...
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19597832
Sub a1()
Dim str1, str2() as String
str1 = InputBox("SearchValue")
Do
Dim valuefound as Boolean,s2row as Integer
s2row=1

For i=1 to Sheet1.Usedrange.Rows.Count
   valuefound = False
   For j=1 to Sheet1.Usedrange.Columns.Count
          if UCase(Sheet1.Cells(i,j)) = UCase(str1) then
               valuefound = True
               Exit For
           End if
  Next
   if valuefound = True then
        Sheet1.Rows(i).Copy Destination:= Sheet2.Rows(s2row)
        s2row = s2row + 1
  End if
Next
str1 = InputBox("SearchValue")

Loop while(str1<>"")
End Sub

0
 
LVL 11

Author Comment

by:bsharath
ID: 19597848
Now another issue.
When i search for Sharath it is coping to sheet 2 and again when i search for ramesh it is over righting the same row.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19597910
Issue solved
Sub a1()
Dim str1, str2() as String
str1 = InputBox("SearchValue")
s2row=1
Do
Dim valuefound as Boolean,s2row as Integer


For i=1 to Sheet1.Usedrange.Rows.Count
   valuefound = False
   For j=1 to Sheet1.Usedrange.Columns.Count
          if UCase(Sheet1.Cells(i,j)) = UCase(str1) then
               valuefound = True
               Exit For
           End if
  Next
   if valuefound = True then
        Sheet1.Rows(i).Copy Destination:= Sheet2.Rows(s2row)
        s2row = s2row + 1
  End if
Next
str1 = InputBox("SearchValue")

Loop while(str1<>"")
End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19597930
I get this.

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Duplicate declaration in current scope
---------------------------
OK   Help  
---------------------------
0
 
LVL 13

Accepted Solution

by:
hiteshgoldeneye earned 2000 total points
ID: 19597976
Sub a1()
Dim str1, str2() as String
Dim valuefound as Boolean,s2row as Integer

str1 = InputBox("SearchValue")
s2row=1
Do


For i=1 to Sheet1.Usedrange.Rows.Count
   valuefound = False
   For j=1 to Sheet1.Usedrange.Columns.Count
          if UCase(Sheet1.Cells(i,j)) = UCase(str1) then
               valuefound = True
               Exit For
           End if
  Next
   if valuefound = True then
        Sheet1.Rows(i).Copy Destination:= Sheet2.Rows(s2row)
        s2row = s2row + 1
  End if
Next
str1 = InputBox("SearchValue")

Loop while(str1<>"")
End Sub

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question