Hi,

I don't really know how to describe it exactly, but I want to do
something like this:

I need to insert a screen resolution inside a cell. It has to be
displayed as '800*600', but I need to work with the 2 values seperatly.
So I think it's needed to put these 2 values in seperate cells. (if
there's an other way, like calculating with ony a part of a cell, let
me know)

the thing I had in mind is:

2 cells: | 800*|600 |
displayed as | 800*600 |

is this possible?

thx in advance!

Sven


--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657

What about storing in one cell (say A1) formatted as text: 800*600
and referring the numbers like
LEFT(A1,3) (800)
RIGHT(A1,3) (600)

or if you have to calculate with these numbers: VALUE(LEFT(A1,3)) ...


Regards,
Stefi

€žnaaitie€� ezt Ã*rta:


Hi,

I don't really know how to describe it exactly, but I want to do
something like this:

I need to insert a screen resolution inside a cell. It has to be
displayed as '800*600', but I need to work with the 2 values seperatly.
So I think it's needed to put these 2 values in seperate cells. (if
there's an other way, like calculating with ony a part of a cell, let
me know)

the thing I had in mind is:

2 cells: | 800*|600 |
displayed as | 800*600 |

is this possible?

thx in advance!

Sven


--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657

ok thx!
I didn't know that was possible with Excel.

Now, I tried some codes & I managed to develop the code I needed. The
code covers like a whole line, but hey.. it works!

thx for the push!

grtz


--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657

hm seems like I have an other problem:

what I want to do: there's a column of values. I want to search the
first 4 letters of each cell in it, to know if it's equal to a given
value.
However, I have to do this 6 times. The first one would give the first
cell where it's in, the second one the second cell, etc..

A1: | abcd.. |
A2: | efgh.. |
A3: | abcd.. |
A4: | rstq.. |
A5: | abcd.. |

C1 would be something like:

Code:
--------------------

from A1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------

& would return 'A1'

C2 would be:

Code:
--------------------

from [content of C1]+1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------

& would return 'A3'

I just dont know how to do this in excel.

grtz & thx in advance


--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657

hm seems like I have an other problem:

what I want to do: there's a column of values. I want to search the
first 4 letters of each cell in it, to know if it's equal to a given
value.
However, I have to do this 6 times. The first one would give the first
cell where it's in, the second one the second cell, etc..

A1: | abcd.. |
A2: | efgh.. |
A3: | abcd.. |
A4: | rstq.. |
A5: | abcd.. |

C1 would be something like:

Code:
--------------------

from A1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------

& would return 'A1'

C2 would be:

Code:
--------------------

from [content of C1]+1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------

& would return 'A3'

I just dont know how to do this in excel.

grtz & thx in advance

*update*
I think I can work something out with the MATCH-function in excel.
This, however, only given me the index of the 1st matching cell, while
I need ALL matching cells.
Is there one that will return a matrix?


--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657

Hi Naaitie,

You should have started a new thread with this question!

Here is a macro doing the job!
Make the first row a header row otherwise Autofilter doesn't work correctly:

A1: | header |
A2: | abcd.. |
A3: | efgh.. |
A4: | abcd.. |
A5: | rstq.. |
A6: | abcd.. |


Sub abcdfilter()
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=abcd*", Operator:=xlAnd
Dim wholerng As Range, filteredrng As Range
Dim cell As Range
Set wholerng = ActiveSheet.AutoFilter.Range.Columns(1)
Set wholerng = wholerng.Offset(1, 0).Resize(wholerng.Rows.Count - 1)
On Error Resume Next
Set filteredrng = wholerng.SpecialCells(xlVisible)
On Error GoTo 0
If Not filteredrng Is Nothing Then
filteredcount = 1
For Each cell In filteredrng
filteredcount = filteredcount + 1
Range("C" & filteredcount) = cell.Address(False, False)
Next cell
End If
Selection.AutoFilter
End Sub

Regards,
Stefi

€žnaaitie€� ezt Ã*rta:


hm seems like I have an other problem:

what I want to do: there's a column of values. I want to search the
first 4 letters of each cell in it, to know if it's equal to a given
value.
However, I have to do this 6 times. The first one would give the first
cell where it's in, the second one the second cell, etc..

A1: | abcd.. |
A2: | efgh.. |
A3: | abcd.. |
A4: | rstq.. |
A5: | abcd.. |

C1 would be something like:

Code:
--------------------

from A1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------

& would return 'A1'

C2 would be:

Code:
--------------------

from [content of C1]+1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------

& would return 'A3'

I just dont know how to do this in excel.

grtz & thx in advance

*update*
I think I can work something out with the MATCH-function in excel.
This, however, only given me the index of the 1st matching cell, while
I need ALL matching cells.
Is there one that will return a matrix?


--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657

Hi Stefi,

thx for your reply.

As I'm new at macros, I looked for a way how to use them. I inserted
the macro, but I couldn't really figure it out what it was doing. In
column C, he adds either a 1 or a 2. As the term 'abcd*' wasnt in any
of the A'cells, I dont get it.

So, I searched for an other way to do it. Here's what I've come up
with:

every search criteria, I want an output of 6 cells. First, I check
wether a value corresponds to the criteria.
Afterwards, I get the adress of the first item (for example A5), and I
compare the criteria with the cells beneith this cell (so A6-A..) to
get a second match.
This keeps on going till the 6th item is found.
I also need 6 temporary cells for the job. When I compare a value, I
instantly keep its location. Otherwise, the 6th term would have to
search for the first item again, then the second, then..., to get the
adress where he has to start looking for.

Not a nice solution, but it works.

thx :)


--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657