Picking up everything from a certain column

[firelex]

Hello, all!
My problem is:
I have such a table:
============================================
TYPE | PART1 | PART2 | PART3 | .........
---------------------------------
bold | | X | | ...........
---------------------------------
normal | X | | X | ............
---------------------------------
thin | X | X | | ............
---------------------------------
and so on ..............
---------------------------------

I need a macro to get all the TYPE's for each PART, basing on those X's in th corresponding column.

E.g. for PART1 I should get {normal thin}.

Any ideas?
Thanks

 

[deetee2k]

The sub below should do it. I'm assuming your table starts at "A1".

HTH

D.

Sub findMyXs()
Dim i As Integer
Dim val, str As String
Dim c, colRange, r, rowRange As Range
' Setup two range objects. one for column labels
Set colRange = Sheets(1).Range("B11&quot
' ...and one for row labels
Set rowRange = Sheets(1).Range("A2:A4&quot
Range("B1&quot.Select
i = 1
For Each c In colRange
For Each r In rowRange
' val is "x" or empty. Offset moves in one column
' to read if the cell contains an "x" or not.
val = r.Offset(0, i).Value
If (val = "x&quot Then
str = str & r.Value & " "
End If
Next r
MsgBox c.Value & ": { " & str & "}"
' Re-initialise for the next loop
str = ""
i = i + 1
Next c
End Sub "I want to play..."
(Guess the X-Files episode!)

 

[DaleWatson123321]

firelex,

A macro might indeed by your best option, depending on the specifics of your situation.

If, however, your objective is to produce a complete summary table of results, then I've created an example file for you that produces the exact results you describe.

This file can also serve as an example of how to utilize Excel's database functions for other situations.

I hope this can help. If you'd like this file, just email me and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[firelex]

I´ve tried the Sub of deetee2k - it´s almost the thing I wanted -> eeh... Could I save or print that "str" ?

 

[deetee2k]

Glad to hear it worked! It outputs the 'str' in a message box. You could change the sub into a function and return the 'str' to the calling environment. Something like so,

Public Function findMyXs() as String
// Insert code here
...
findMyXs = str
End Function

Then call this function like so,

dim str_val as String
str_val = findMyXs()

HTH,
D. "I want to play..."
(Guess the X-Files episode!)

 

[firelex]

Thanks deetee2k!
I've looked through some tutorials and found out how to put the whole output into a file and every time the macro finds anything - I get it written.
The last (I hope) proble left is : I need to start the macro from a certain column. It is every time another one. So I thought - the best thing is to start from the column, where the cursor currently is.
Do you know how can I get the number of such column?
It'll be for sure something like "col.Selected" (this one doesn't work ;-]] )
If the cursor isn't inserted yet, what shall I get in this "col.Selected" request?

 

[deetee2k]

To get the address of the cell the user has selected:
ActiveCell.Address(False, False)
...will return 'A1'. Take out the Address parameters to get the address in absolute form '$A$1'

or insert the following to have the user specify a starting cell:

On Error Resume Next
Dim ret As String
ret = InputBox("What cell do you want to start from?&quot
Range(ret).Select

'course you will have to do some error checking on the input string 'ret'.

There could be a smarter way to do this using userforms but this is the quickest. Good luck!

D. "I want to play..."
(Guess the X-Files episode!)