I'm gonna skip #1. I bet it could be done, but it sounds like an unusual
request and sometimes there are better ways.


#2. You could select a single cell
Edit|goto|special
check constants (if you want to avoid the cells with formulas)
Uncheck Text, Logicals, errors and leave Numbers checked.
click ok
format those selected cells the way you want.

#3. Same as #2.
but choose Formulas and errors

For both #2 & #3. Record a macro if you want to mechanize it.

#4. You'd need a macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No cells with text constants"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = Trim(myCell.Value)
'or
'myCell.Value = Application.Trim(myCell.Value)
Next myCell

End Sub

There's a difference between VBA's trim and Excel's Trim. VBA's trim will
remove leading and trailing spaces. Excel's trim does the same, but it also
cleans up repeated spaces within the string:

Using _ as a space:
__asdf_____asdf____
using VBA's trim would become:
asdf_____asdf
using excel's trim (application.trim)
asdf_asdf

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

====
Back to #1.
Maybe you could match up on the values in two columns.

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

trav wrote:

I have several questions:

I have three worksheets that are doing multiple vlookup s .

it is a pain to go through and set the range for all of them,

is there a way i can have a macro go through and set a named range for
a range with the same value for column A,

EB101 CAN THE ELECTRONICS BOUTIQUE GBA02027BL
EB101 CAN THE ELECTRONICS BOUTIQUE PST08008
EB101 CAN THE ELECTRONICS BOUTIQUE PST39925
EB101 CAN THE ELECTRONICS BOUTIQUE PST75133
EB101 CAN THE ELECTRONICS BOUTIQUE PST75167
EB101 CAN THE ELECTRONICS BOUTIQUE XBX49915
EB101 CAN THE ELECTRONICS BOUTIQUE XBX49915BL

so that would be range eb101

SG104 CAN SOLUTIONS TO GO PST39931
SG104 CAN SOLUTIONS TO GO XBX49916

so that would be range sg104
and so on..

the spread sheet is sorted based on column A,

My second question is can you set a range and do a macro that sets
every cell with a numerical value with a background color of 6,
I know how to do this by running through every cell in a loop, but is
there a better way

third question
Kind of relates to the second question, can i set a range and for every
cell that has a formula error in it, and clear the cell.

Final question
is there a way that i can go through and trim() all the cells of a
spreadsheet, or of a column.

I know thats kind of a lot of question, but they are all kind of
related to the same thing, how to do _blank_ to a range of cells.

thank you in advance
trav

--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=516020

--

Dave Peterson