questions regarding range's
 

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

questions regarding range's
 

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

questions regarding range's
 

First of All

THANK YOU!!!!

Dave you rock. thank you so much

so 3 of my 4 problems are solved.

I am a little confused about the first one though.
I am somewhat familiar with macros, and a little VB.

i was thinking for the selecting a range, i would have a macro that ran
through checking previous values against current values an if it find a
unique one then set a range. I can do "finding the unique values", the
problem is that i do not know how to set a range in a macro/VB ,
bascially i want to be able to name a cell range, so col 3 row 23 to
col 6 row 45 = ie101

that would be like Range C23:F45 is named ie101

that way when i call my vlookup, i can just call ie101, and then it
fits into my macro function a lot better, and makes my life a lot
easier.

I am completely unfamiliar with the idex function and match, so i am
not sure if that is what it does. but i didn't really understand what
you were suggesting.


thank you again for all the help.


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

questions regarding range's
 

I was just questioning what you really trying to do with your first question.

It kind of looked like you wanted to define a bunch of names so that you could
have your =vlookup() statement be limited to a single named range.

If the reason you were doing that was so that you could have a key show up in
multiple spots, but you want to only look at one specific range, you may not
need the names at all.

For instance, say you have a key of PST08008. It shows up in the "EB101 CAN"
group and it also shows up in "SG104 CAN" and it shows up in a bunch of other
groups as well.

You could define a name for the "EB101 Can" and another name for the "SG104 Can"
and another and another...

Or you could just change your =vlookup() to a different function that looks for
matches on the first column and for matches with the second column.

That's what that last suggestion did. It matches on multiple fields and returns
something when all those multiple fields match your specifications.

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))


trav wrote:

First of All

THANK YOU!!!!

Dave you rock. thank you so much

so 3 of my 4 problems are solved.

I am a little confused about the first one though.
I am somewhat familiar with macros, and a little VB.

i was thinking for the selecting a range, i would have a macro that ran
through checking previous values against current values an if it find a
unique one then set a range. I can do "finding the unique values", the
problem is that i do not know how to set a range in a macro/VB ,
bascially i want to be able to name a cell range, so col 3 row 23 to
col 6 row 45 = ie101

that would be like Range C23:F45 is named ie101

that way when i call my vlookup, i can just call ie101, and then it
fits into my macro function a lot better, and makes my life a lot
easier.

I am completely unfamiliar with the idex function and match, so i am
not sure if that is what it does. but i didn't really understand what
you were suggesting.

thank you again for all the help.

--
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