Free Microsoft Excel 2013 Quick Reference

Can a UDF be used on an array of cells?

I've been using this "IsRed" UDF for some time now to identify cells in
which data has been changed to red.

Public Function IsRed(rg As Range) As Boolean
Application.Volatile
IsRed = rg.Font.ColorIndex = 3
End Function

For example, in cell B1, I would enter this formula to test cell A1:

=if(IsRed(A1),1,0)

I have 2 questions regarding this:

1.) If I change the entry in cell A1 to red, B1 will automatically
change to 1. Conversely, however, if I change A1 back to black (or any
other color), B1 does not automatically change to 0. I have to hit F9
in order for that to happen, and if I (or the user) forgets to hit F9,
we've got problems. Is there any way to have it automatically change
to 0 just like it automatically changes to 1?

2.) In a particular worksheet, there are about 500 rows of data in 33
fields that go from column A through column AG. The user will indicate
changes he makes to data by changing the color of that data to red, and
I need to test for any record where the contents of any cells have been
changed to red. I could enter 33 corresponding if(IsRed) formulas in
cells AJ through BP, but at 33 such formulas per record times 500
records, that's a lot of formulas! Is there a way to use if(IsRed) on
an array of cells? The following obviously doesn't work, but it
illustrates what I'm trying to accomplish: in cell AJ2
=if(IsRed(A2:AG2),1,0)

Many thanks,
Paul


I've been using this "IsRed" UDF for some time now to identify cells in
which data has been changed to red.

Public Function IsRed(rg As Range) As Boolean
Application.Volatile
IsRed = rg.Font.ColorIndex = 3
End Function

For example, in cell B1, I would enter this formula to test cell A1:

=if(IsRed(A1),1,0)

I have 2 questions regarding this:

1.) If I change the entry in cell A1 to red, B1 will automatically
change to 1. Conversely, however, if I change A1 back to black (or any
other color), B1 does not automatically change to 0. I have to hit F9
in order for that to happen, and if I (or the user) forgets to hit F9,
we've got problems. Is there any way to have it automatically change
to 0 just like it automatically changes to 1?

2.) In a particular worksheet, there are about 500 rows of data in 33
fields that go from column A through column AG. The user will indicate
changes he makes to data by changing the color of that data to red, and
I need to test for any record where the contents of any cells have been
changed to red. I could enter 33 corresponding if(IsRed) formulas in
cells AJ through BP, but at 33 such formulas per record times 500
records, that's a lot of formulas! Is there a way to use if(IsRed) on
an array of cells? The following obviously doesn't work, but it
illustrates what I'm trying to accomplish: in cell AJ2
=if(IsRed(A2:AG2),1,0)

Many thanks,
Paul

I have a database of vehicle information (3000 rows by 20 columns) for cars that are at several different facilities. What I am trying to do is have a single facility # be selected from a drop-down list and in return an array of cells will be filled in with the information from the database that corresponds to that facility # only (approx. 30 rows by 20 columns).

I have the feeling that I will have to use VBA to get what I need, but have no idea where to start on finding the coding that is going to work.

Any help is greatly appreciated!

XL98: ParamArray Must Be Declared as an Array of Variant

... http://support.microsoft.com/kb/151582/EN-US/) XL: Methods to Use Custom Functions with Varying Arguments

Help for a novice please!!

On the active worksheet I want to be able to select a single cell and then
assign a macro (that I will run from CTRL A) to select an array of cells
that is 26 columns wide by 27 rows deep from that single cell) and copy that
selection to a worksheet entitled "outlet summary" to cell A12.:Z38.

e.g. I select cell A435 on the active sheet and when I press ctrl A it will
copy the array of cells ref A435:Z461 (i.e. 26 columns by 27 rows where the
cell selected is top left cell) to worksheet "outlet summary" into cells
A12:Z38.

I know this is probably very simple but I am struggling to identify the cell
reference of the selected cell in VBA.

Would very much appreciate some help.

Thanks in anticipation.

Is there anyway to easily set up an array of cells, wherein each
element of the array would be a cells object? Do I need to set up a
class or is there something in Excel that already does this?

Im using Excel 2002.

-Abe

Can a MACRO be written to run repetitively until cells equals zero?

I have twenty-seven different customers who are requesting to alter their contractual entitlements to electric power in kilowatt hours over the course of a year. It is an exchange in which I am required to balance their requests to the total of preexisting contractual entitlements for all customers each month and the total of each customer’s individual prexisting entitlement for the year. I have two calculations which I have built into a MACRO that seems to get me closer to balance each time I run them however I would like to embed that MACRO into another MACRO that would test twelve different cell values after my calculation MACRO runs and then repeat the calculation MACRO until those twelve cells equal zero. I would really appreciate anyone who may have done something similar and has an example.

Thanks,

JP

hi there

Looking to take an array of cells and paste them from left to right as right to left.

for example if I have 3,2,4 and want them to be 4,2,3.

The paste special option will make them vertical and then horizontally but not how I am expecting them to be.

I have an array of cells, B3:J34 on sheet2.

On sheet, 1, column, I have a range of values that can be found in the
array on sheet2. I'd like to put a function on column b, sheet1 to
return the value in row 2, above the searched value from sheet1 ,
column A. Does that make sense?

Basically, I want to search for a value within an array, and return
the value in row 2 when the searched value is located.

Thanks

I have a sheet with inventory data

A - stock code
B - usage
C - stock on hand
D - item name

Usage data is shown by month so there could be 0 - 12 rows for each stock code. Stock on hand is the current stock available (same for all rows of a single stock code).

I need to find all cases where total usage for the year is 0, but we have stock on hand. That means for each stock code, I need to find all the rows where it appears and return a sum of usage for all rows. I would then like to display:

stock code, total usage for year, stock on hand, item name

I'm not sure what combination of functions can help me with this. VLOOKUP only returns the first instance of the stock code and usage. I need a function that will return an array of numbers (all usage values) or something to that effect. How can this be done?

Thanks,
Mike

I am using a worksheet to track channel assignments for a data acquisition
system. The sheet has an array of cells which define by rows the input cards
in the system. The columns are the channels for each card. The entries are
short mnemonics which identify which signal is connected to a particular
channel. I would like to make sure that the same signal name cannot be
entered twice in the array. This validation would assist in relocating
signals if the system has to be reconfigured and would assist in assigning
signal names to new proposed signals.

Can an array of cells be selected and a validation check applied to make
sure that only one occurence of a data entry is made within the array?

I am looking for a macro to search a column of text based on an array of names. Each cell in the column is basically a text paragraph.

If the name is found in the paragraph, the name and the following 15 characters need to be copied from the cell into another worksheet.

The array of names are fixed and are in a worksheet area. names are "adam, john, steve", etc. and are in range a1...a15 in worksheet called "names"

data is in a table in worksheet called "rawdata" I am searching column f which has about 1170 rows for the text.

Any help will be appreciated.

I defined a name that was equated to an array of values returned by a
function. After typing the definition, I tried pressing ctrl-shift-enter.
The function works properly, but I don't see the definition in braces {}
when selecting the name (in the Define Name dialog box).

Can a name be entered as an array? Reducing calculation time is the name of
the game here.

Hi there,

I would like to create an array of addresses based on adding the values in a range to a string. Excel resolves the array to a single value and returns a single address, rather than an array of addresses.

Given:

A_Range_of_Values = {2,4,5,6}

I would like:

{="SomeSheetName!A" & A_Range_of_Values}

To return:

{SomeSheetName!A2,SomeSheetName!A4,SomeSheetName!A5,SomeSheetName!A6}

Instead of:

SomeSheetName!A2

Any help with this or suggestions of a different approach would be appreciated.

Thanks folks,

Jason.

Hi,

I am having trouble referencing an array of cells within the IF
function, which need to be searched for a certain criteria.

I need the formula to search for a criteria, and if found return the
value next to the criteria.

If the criteria is not found then I want value next to another fixed
criteria (which I know will always be there) to be returned.

Any suggestions?

Thanks

--
selvaraj
------------------------------------------------------------------------
selvaraj's Profile: http://www.excelforum.com/member.php...o&userid=25674
View this thread: http://www.excelforum.com/showthread...hreadid=390928

I originally posted this in the charting section - but no one seemed to know how to handle it. Can a program be written to do this?

I think this is an extremely tough one, so I apologize in advance. We've been working for weeks to try to automate the creation of something like the attached JPEG file, which we create in PowerPoint. We manually update it and it is cumbersome and causes much data entry error.

A typical data set would like like this:

Team | Win Percent | Prep Time (0-100)
Team A | 1% | 1
Team L | 25% | 80
Team HH | 99% | 90

The desired display is too difficult to explain - that's why I attached the file.

We've tried bubble charts and bar charts of all styles, but nothing seems to be flexible enough for what we need.

ANY help is greatly appreciated, we're stuck!

Thanks in advance.

Hello all.

I have a problem using the RANK function with an array of cells.

I have numbers placed in (for example) cells A1:A5 and would like to know their rank.
I select B1 and drag my mouse down to select the cells B1:B5.
I type "=rank(a1;$a$1:$a$5)" (without the quotes) and press Ctrl+Shift+Enter.

What happens is that it gives me the rank of A1 in each of the cells of b1:b5. It doesn't increment the value 'a1' so as to give the concording rank.

So instead of having :
100 - 1
75 - 3
100 - 1
50 - 5
75 - 3

I have :
100 - 1
75 - 1
100 - 1
50 - 1
75 - 1

Does anybody know how I can have it that it gives the correct values ?

One solution is to only select the first cell, do the manuipulation and copy the cell to the following cells, but I would much rather be able to do it in one swoop.

Many thanks for any help you can provide.

Anton HARRIS

I am trying to write a macro that will select an array of worksheets. The number of worksheets may vary from book to book so I tried using the following:
Sheets(Array("Sheet (1)", Sheets(Sheets.Count))).Select

but this doesn't work. Can anyone help me out please

I have one array of cells (5x5). There is a number in each cell, and several of them are not unique. I have another array of cells that needs to fill up with values from the first array, but must only take a value if it isn't already in the second array.

For example, in this source array:

1 2 8 5 3
4 9 2 3 1
9 4 3 7 5
6 5 6 0 9

The second array would look like this:

1 2 8 5 3
4 9 7 6 0

Each number would be in a separate cell, and would need to look at the preceding cells to determine which values it can receive, if any.

Any ideas?

I have been stuck on this macro and hope someone can help. I need a macro that copies an array of cells into a specific set of cells. The problem is I need to match or link the cells from where it is being copied to the destination cells. For example, cell B7 copied to W12 and so on in random order. Thanks in advance any help

Hi,

I am having trouble referencing an array of cells within the IF function, which need to be searched for a certain criteria.

I need the formula to search for a criteria, and if found return the value next to the criteria.

If the criteria is not found then I want value next to another fixed criteria (which I know will always be there) to be returned.

Any suggestions?

Thanks

My current code searches a range of cells, and if it finds a particular string it replaces the string w/ a substitue string. What I would like to do is alter my list to somehow make it more generic in nature.

Here are the details:
The list is a color list, and I log every colorway as it is created. This makes for a long list, and it is difficult to keep track of all the colors as they are added.
Example: My list has these color combos and are formatted as such:

White
White/Black
White/Red/Black
White/Black/Red
etc...
I have a replacement value for all colors that more than one color as the first color and a plus sign, the base color, if only one color stays constant, e.g.
White = White
White/Black = White+
Whte/Red/Black = White+
White/Black/Red = White+

What I was thinking that I might do is any colors with a "Slash" would be changed to the base color and a "+" sign.

This would eliminate the need to log every potential colorway. Currently I have 589 colorways and more will be added.

The color database is stored in a sheet "RepTerms" in a named range, "Cls".

Any ideas how I might accomplish this?
Note: this used to be used on an array of two sheets, but is now only one sheet. I am modifying it slowly as to not break what currently works.

Here is my current code:

Code:
 
Sub ColorSwapAR()
     Dim wsFF As Worksheet, wsVB As Worksheet, wsar As Worksheet
     Dim c As Range, rng As Range
     Dim lrwSource As Long, ai As Long
     Dim wsArray
     Dim wbn As String, FF As String
     'Dim VB As String
     wbn = "MasterImportSheetWebStore.xls": FF = "PCCombined_FF" ': VB = "PCCombined_VB"
     wsArray = Array(FF)
     'wsArray = Array(FF, VB)
     For ai = LBound(wsArray) To UBound(wsArray)
          Set wsar = Worksheets(wsArray(ai))
          With wsar
               lrwSource = lr(wsar, 1)
               For Each c In Worksheets("RepTerms").Range("Cls")
                    .Columns(14).Replace what:=c, replacement:=c.Offset(, 1), LookAt:=xlWhole, _
                                        searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
                                        ReplaceFormat:=False
               Next c
          End With
          Application.ScreenUpdating = True
          Application.ActiveSheet.Calculate
     Next ai
     End Sub
Thanks,

Doug

Hi I would like to if possible create a formula to look into an array of cells and pick out rows conditionally based in values in two columns.

If the columns contain certain values I would like to make an output list containing all columns and the applicable rows into another list.

I know this is very hard to explain so i have attached an xls file for you to have a look at. If you have time it would be a great help because I am really scratching my head over this one!!

Thanks

Rob

Frank -

I'm replying to both of your posts. First a few questions:

1.
>>I am trying to handle multiple datacollections and thought the array would
be an easier way to do it.
'Region Number is in Column A
> 'xValue (Revenue) is in Column C
> 'yValue (Var Margin) is in Column E
> 'D and E left blank to force non-contiguous logic
>
> 'There is a header row in Row 1
> 'There is actual data in Rows 2 through 11 for testing purpose only
> 'It also assumes a pre-existing chart of the Revenue and Margin
>
> ' The code to remove duplicates is based on a tip by J.G. Hussey,
> ' published in "Visual Basic Programmer's Journal"
>
> Dim AllCells As Range, Cell As Range
> Dim NoDupes As New Collection
> Dim i As Integer, j As Integer
> Dim Swap1, Swap2, Item
>
> ' This area defines the reange in which I want to look for unique items
> Set AllCells = Worksheets("Data").Range("A2:A11")
>
> ' The next statement ignores the error caused
> ' by attempting to add a duplicate key to the collection.
> ' The duplicate is not added - which is just what we want!
> On Error Resume Next
> For Each Cell In AllCells
> NoDupes.Add Cell.Value, CStr(Cell.Value)
> ' Note: the 2nd argument (key) for the Add method must be a string
> Next Cell
>
> ' Resume normal error handling
> On Error GoTo 0
>
> 'Define the SeriesCollection
> Dim SeriesCollection As Collection
>
> 'Add a ChartSeries for each unique item
> Charts("Chart1").Activate
> For Each Item In NoDupes
> ActiveChart.SeriesCollection.NewSeries
> Next Item
>
> 'this is test setting only ... will add code to determine actual
> Dim TotalRows As Integer
> TotalRows = 10
>
> 'Extend the datapoints
> Dim DataRange As String
> Dim DataSeries As Integer
> Dim Row As Integer
>
> Charts("Chart1").Activate
> Row = 2
> For Row = 2 To TotalRows + 1
> 'Determine the Region Number for the DataSeries
> DataSeries = Sheets("Data").Range("A" & Row).Value
> ActiveChart.SeriesCollection(DataSeries).Select
>
> 'Determine the X and Y Value
> DataRange = "C" & Row & ",E" & Row
>
> 'Extend the series
> ActiveChart.SeriesCollection.Extend
> Source:=Sheets("Data").Range(DataRange), _
> Rowcol:=xlColumns, CategoryLabels:=True
> Next Row
>
> End Sub
>
>
>
>

Hi all

I'm trying to figure out how I can return an index to an array of values
using "MATCH".

It should be a simple MATCH function but after digging through the questions
& answers on "match" and trying using help for a couple of hours I gave up.

I have an array, {"NONE","NEGLIGIBLE","MILD","MODERATE","HIGH","SEV ERE"} and
want to match this array against the value out of a cell.

Problem is the double-quotes, as far as I can tell.

This function below works,
=MATCH("HIGH",{"NONE","NEGLIGIBLE","MILD","MODERAT E","HIGH","SEVERE"},1)

But these functions below always fail, the first is an attempt to use a
literal value, the second is an attempt to use a value out of a cell

=MATCH(CONCATENATE("""",HIGH,""""),{"NONE","NEGLIG IBLE","MILD","MODERATE","HIGH","SEVERE"},)

MATCH(CONCATENATE("""",N13,""""),{"NONE","NEGLIGIB LE","MILD","MODERATE","HIGH","SEVERE"},)

What am I doing wrong ?