?
Solved

Excel sheet macro which will search a text and ask me to copy which colums

Posted on 2007-07-26
39
Medium Priority
?
393 Views
Last Modified: 2010-03-05
Hi,

I need a macro which will search for a excel sheet content and ask me which all colums it needs to cop.Say if i need to search sharath and i have 20 colums next to it.But i just need to copy the contents A,C,F,J.How do i do it.

regards
Sharath
0
Comment
Question by:bsharath
  • 21
  • 18
39 Comments
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19576004
Sharath this question is in the wrong zone again.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19576040
Sorry.Is there any way to change after the Q is posted.?
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19576056
you can request administrator by putiing an administrator question to change the zone
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 11

Author Comment

by:bsharath
ID: 19576139
OK THANKS
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19576287
by the way sharath, your question is not clear to me
>Say if i need to search sharath and i have 20 colums next to it.But i just need to copy the contents A,C,F,J.

Can you explain this a little more?

0
 
LVL 11

Author Comment

by:bsharath
ID: 19579964
Say i have
Sharath in Colum C
Age in B
City in D
Country in A

I want a macro to ask me what all colums to copy.Say i select B and D
And another box which will ask me what to search and i say "Sharath"
So the results to a new page should be Age City Sharath

THX
Sharath
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19580300
try this macro
Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),";")  'Enter columns separated by ; e.g (B;D)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For i=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,C)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet2.Cells(s2row,k) = Sheet1.Cells(i,str1(k-1)
                 Next
                 s2row = s2row+1
          End if
    Next
Next

End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19580363
I get this.

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

For control variable already in use
---------------------------
OK   Help  
---------------------------
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19580580
sorry try this macro
Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),";")  'Enter columns separated by ; e.g (B;D)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,C)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet2.Cells(s2row,k) = Sheet1.Cells(i,str1(k-1)
                 Next
                 s2row = s2row+1
          End if
    Next
Next

End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19580595
Checking on this.Can you help me with this Q.Need it urgent

http://www.experts-exchange.com/?qid=22724761
0
 
LVL 11

Author Comment

by:bsharath
ID: 19587878
I get this error.
---------------------------
Microsoft Visual Basic
---------------------------
Can't execute code in break mode
---------------------------
OK   Help  
---------------------------


I get this error in this point.

                      Sheet2.Cells(s2row,k) = Sheet1.Cells(i,str1(k-1)
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19588427
Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),";")  'Enter column nos separated by ; e.g (1;3)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,j)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet2.Cells(s2row,s2col) = Sheet1.Cells(i,Val(str1(k-1)))
                      s2col = s2col + 1
                 Next
                 s2row = s2row+1
          End if
    Next
Next

End Sub
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590854
did u try it
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590891
Checked but not working.No error.
Its asking me for the colums to search.I specify A,B,C
then it asks me text to search.I specify Sharath
But does not copy the colums to new sheet
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19591327
Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),",")  'Enter column nos separated by , e.g (1,3,)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,j)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet1.Columns(k).Copy Destination:= Sheet2.Columns(s2col)                  
                      s2col = s2col + 1
                 Next
                  Sheet1.Columns(j).Copy Destination:= Sheet2.Columns(s2col)
                 s2row = s2row+1
          End if
    Next
Next

End Sub
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19591331
Enter Column Nos like this 1,2,3,
not A,B,C
0
 
LVL 11

Author Comment

by:bsharath
ID: 19591451
The copy box is asking me to enter 2 times.Ebven when i enter i dont et the colums copied.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19591539
the first time you have to enter the column nos.
second time the search value
0
 
LVL 11

Author Comment

by:bsharath
ID: 19591563
Ok got it,

When i type the colum 1,3 in first box and Sharath in 2nd box.
It only copies colum A(1) 2 times to sheet 2.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19592223
enter 1,3,
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19593413
if possible upload your file in 2003 format.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19593671
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19593748
it is in 2007 format
0
 
LVL 11

Author Comment

by:bsharath
ID: 19593781
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19597310
Use the following macro, type 2,3, and search value as Sharath and see the result
Sub columncopy()
Dim str1 As Variant, searchvalue As String, s2row As Long
str1 = Split(InputBox("Columns to Copy"), ",") 'Enter column nos separated by , e.g (1,3,)
searchvalue = InputBox("Search Value")
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j = 1 To Sheet1.UsedRange.Columns.Count
          If Sheet1.Cells(i, j) = searchvalue Then
                 For k = 1 To UBound(str1)
                      Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)
                      s2col = s2col + 1
                 Next
                  Sheet1.Columns(j).Copy Destination:=Sheet2.Columns(s2col)
                 s2row = s2row + 1
          End If
    Next
Next

End Sub

0
 
LVL 11

Author Comment

by:bsharath
ID: 19597460
I get 1004 error.
Debug
  Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)

Is it case sensitive?
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19597777
when you get the error can you tell me the values of s2col and str1(k-1) by typing the following in the immediate window

?s2col
?str1(k-1)

Sub columncopy()
Dim str1 As Variant, searchvalue As String, s2row As Long
str1 = Split(InputBox("Columns to Copy"), ",") 'Enter column nos separated by , e.g (1,3,)
searchvalue = InputBox("Search Value")
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j = 1 To Sheet1.UsedRange.Columns.Count
          If UCase(Sheet1.Cells(i, j)) = UCase(searchvalue) Then
                 For k = 1 To UBound(str1)
                      Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)
                      s2col = s2col + 1
                 Next
                  Sheet1.Columns(j).Copy Destination:=Sheet2.Columns(s2col)
                 s2row = s2row + 1
          End If
    Next
Next

End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19597820
You mean to say
In the 1st box
?s2col
In the second box
?str1(k-1)
I get no results.If this is not the way.Please tell me.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19597825
I have tried the macro, it works fine, can you tell me what exactly are you entering in the input boxes
0
 
LVL 11

Author Comment

by:bsharath
ID: 19597841
In the first box i entered A,C,
In the 2nd box Sharath

I have 3 colums in the excel.
name  Age  Sex
Sharath  29  M
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19597867
you have to enter column nos like this 1,3,
not column letters
0
 
LVL 11

Author Comment

by:bsharath
ID: 19597883
Ok in sheet 1 i have this.

Name       Age      Sex
Sharath      35      M
ramesh      23      M

When it asks me for the colum i mention 1,3,
When it asks me to search
Sharath
This is what i get in the sheet 2
Name       Sex      Name
Sharath      M      Sharath
ramesh      M      ramesh

0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19597917
Isnt that what you want?, if not what should be the output in this case
0
 
LVL 11

Author Comment

by:bsharath
ID: 19597937
I should only have this in sheet 2

Name            Sex
Sharath           M
ramesh           M
0
 
LVL 13

Accepted Solution

by:
hiteshgoldeneye earned 2000 total points
ID: 19598017
Here you go
Sub columncopy()
Dim str1 As Variant, searchvalue As String, s2row As Long, donotcopy As Boolean
str1 = Split(InputBox("Columns to Copy"), ",") 'Enter column nos separated by , e.g (1,3,)
searchvalue = InputBox("Search Value")
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j = 1 To Sheet1.UsedRange.Columns.Count
          If UCase(Sheet1.Cells(i, j)) = UCase(searchvalue) Then
                 For k = 1 To UBound(str1)
                      Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)
                      s2col = s2col + 1
                      If Val(str1(k - 1)) = j Then
                          donotcopy = True
                      End If
                 Next
                If Not donotcopy Then
                  Sheet1.Columns(j).Copy Destination:=Sheet2.Columns(s2col)
                  Exit Sub
                End If
                  Exit Sub
               
          End If
    Next
Next

End Sub


0
 
LVL 11

Author Comment

by:bsharath
ID: 19598047
Error.

Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)

Application -defined or object defined errors.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19598062
i hope you have entered column nos like this 1,3,
not column letters

0
 
LVL 11

Author Comment

by:bsharath
ID: 19598113
I have even tried colum letters.
Debug
Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19598139
buddy i dont know whats going wrong here i have tried it here it works fine, can u tell me what exactly you typed
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

850 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