I have a series of data values in non-adjacent columns in an excel spreadsheet.

In the following example, assume the | (vertical bar) refers to the start of

a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a

cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row

(starting with Cell A1) for validating they equal F, and a range of the

alternating cells (starting in column B1) for the range containing the data

to add. How can I specify these ranges (I can't name each cell individually

as I have more than 30 cells to add up in my real life situation and the IF

function allows selection of no more than 30 values)?

In the following example, assume the | (vertical bar) refers to the start of

a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a

cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row

(starting with Cell A1) for validating they equal F, and a range of the

alternating cells (starting in column B1) for the range containing the data

to add. How can I specify these ranges (I can't name each cell individually

as I have more than 30 cells to add up in my real life situation and the IF

function allows selection of no more than 30 values)?

- Select a range of non-adjacent cells in Excel?
- Set a range of non-contiguous cells in 1 column based on non-blanks in another column
- VBA: Macros that select a range of non-consecutive
- Select Non-Adjacent Cell Range in VBA
- Set MyRange for non-adjacent cells and iterate through?
- Suggestion to add ability to merge non-adjacent cells in Excel.
- How to search for a block of two adjacent cells from a table
- Suggestion to add ability to merge non-adjacent cells in Excel.
- Named range(s) of non-adjacent cells return #VALUE! error in array formulas
- Selecting a range of cells in a row to clear
- Keyboard command used to select a range of nonadjacent cells?
- Count number of non-blank cells in a range
- How to Select a range of cells and apply the clear function?
- Debug message pops up when I select a range of cells and delete
- Selecting a Range Of Cells.
- Help with selecting a range of cells
- Selecting a range of cells
- Reusing grouping of non-adjacent cells
- Reusing grouping of non-adjacent cells
- How can I select a range of cells based on a value of a cell?
- Select a Range Based on Active Cell Location?
- Selecting a range of cells with R1C1 notation
- Select a range of cells relative to ActiveCell?
- How do you select a range of cells on another worksheet using the Cells property?

In the following example, assume the | (vertical bar) refers to the start of

a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a

cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row

(starting with Cell A1) for validating they equal F, and a range of the

alternating cells (starting in column B1) for the range containing the data

to add. How can I specify these ranges (I can't name each cell individually

as I have more than 30 cells to add up in my real life situation and the IF

function allows selection of no more than 30 values)?

Using VBA I need to set a range of non-contiguous cells from Column A, where column B is not a blank

eg

A B

1 1234 xxxxx

2 2345

3 3456 xxxxx

4 4567 xxxxx

would set range ("A1, A3, A4")

Any ideas on how to do this?

Thanks!

janie

I need a macro that would select a range of non-consecutive cells.The problem is that the first cell address is never the same(maybe A21 or A28 or anything). But once the address of the first cell is picked up, all the other non-consecutive cells occur at equal offset distances. May be the user could make the initial selection of the first cell address and a macro could be run to pick-up the rest . How could that be done ?

Im trying to create a macro that will do the following. When i record it, it always errors out and doesnt work so im trying to code it manually. Here is an explanation:

I need to Multiply 100 times a range of non-adjacent cells.

ie. the values 0.08125, 0.08000, 0.07875, & 0.07750 are in cells A1,A2,A3,A4 respectively AND 0.07500,0.07625,0.07500,0.07375 are in cells D7,D8,D9,D10.

How can i write a macro that will select all these ranges of cells and multiply the values by 100 so that they end up being 8.125,8.000,7.875,7.750 etc. etc.

Thanks for your help....

all the cells of a specified block of cells, picking out certain cells that

meet a specific criterion, and adding them to MyRange object. These cells

would probably be scattered over the original block of cells and many would

not be adjacent to any other cell in MyRange. Then I would iterate through

all the cells in MyRange to do things, and drop cells out of MyRange until I

have exactly what I want. Is it possible to set up a range of non-adjacent

cells so they can be iterated through? If so, what's the easiest methods to

use to add or delete a cell from such a range?

Thank you.

Ed

am talking about tying two or more cells together so that they behave like

the same cell: you would be able to change data in EITHER ONE and it will

automatically show up in the other cell AND vice-versa.

--

Doyle_D

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I have a table like this

jane....kaser...highschool......A

jack....Anderson....college.....B

Jill....Lovin.....college.......C

I want to check if both jack and Anderson in a same row two adjacent cells.

If both jack+anderson in a same row adjacent cells

as per in my table it should return column 4 value (A).

can I use VLOOKUP?

is there another way ?

please note I want to search for a block of two adjacent cells in a table and return its forth column value.

thanks

am talking about tying two or more cells together so that they behave like

the same cell: you would be able to change data in EITHER ONE and it will

automatically show up in the other cell AND vice-versa.

--

Doyle_D

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

get a #VALUE! error when I try to do any conditional count or sum

calculation with an array formula on either or both of them. Each

range is a selection of 32 non-adjacent cells from a single column.

The cells contain array formulas that return percentages.

Example of range values

I8: 5.9%

I11: 12.1%

I14: 22.3%

I17: 0.0%

I was able to get values returned from simple functions like Max and

Min but the following example gives me the #VALUE! error:

{=SUM(IF((Rng>0),1,0))}

=COUNTIF(Rng,"<.0125")

Any wisdom would be appreciated!! Thanks

is there a way to select a range of cells in a worksheet and clear the data in them?

i DO NOT want the WHOLE ROW to be deleted, only a few selected cells (they are all adjacent to each other)

and i don't think the ActiveSheet.ranges method works as:

1) the coordinates of the cells in the row to be cleared are determined logically by a VBA sub, i know how to get the coordinates, just dunno how to delete the row

2) this needs to be applied in different areas of the worksheet AND across OTHER worksheets as well.

I'm quite sure I have the logic needed to find the coordinates just need to know how to clear the cells

if there is a way to do it that is similar to ActiveSheet.cells ( row, column ) then it would be best

from my understanding the ranges method only takes in letter and number formats in strings... i need something that takes in number and number

thank you!

2)Hold down the [Shift] key while you extend the selection using the up,

down, left, right arrow keys.

3)After the range is selected, and while still holding down the [Shift] key,

press the [F8] key (they release all keys). You will see the word 'ADD' in

the lower right of the status bar.

Repeat above steps until done selecting non-contiguous ranges.

Does that help?

â€¢â€¢â€¢â€¢â€¢â€¢â€¢â€¢â€¢â€¢

Regards,

Ron

"Jessizzle123" wrote:

> does anyone know the keyboard command used to select a range of nonadjacent

> cells?

I want to count the number of non-blank cells in a range from A to Z along a given row.

Then once the loop is done i want it to tell me the row which had the most non-empty cells but its not working.

j

When i select a range of cells and hit delete, a that debug message appears.

The range in question is controlled by the following sytax in VB:

If target.Row < 10 Or target.Row > 13 Then Exit Sub

Application.EnableEvents = False

If target.Column = 8 Then

If IsNumeric(target) = False Then

If Len(target) > 0 Then

target.Offset(0, -2) = ""

End If

End If

End If

Application.EnableEvents = True

End Sub

i have to sometimes select this range of cells all at once and delete, but

how can I prevent the debug message from appearing.

ones they are?

I have a sheet that has a variable number of rows and columns, my code

searches all cells for a certain condition, and when the condition is met, it

needs to select that cell, and the next 8 cells in the same row in order to

copy and paste them somewhere else.

I've tried to use the line: (and various derivatives)

Selection.Offset(0, 8).Select

But this doesn't work.

Can anyone tell me what I'm doing wrong?

Thanks

Neil

different ways there are to do the same thing. Your help is very much

appreciated.

Regards

Mick

"Mick" > wrote in message

...

> This is just a small part of my macro, I do hope the eagle eyed amongst

> you can spot my mistake, all I want to do is select a range of cells in a

> row and copy them?

>

> I don't want to specify the range of cells by naming the rows/columns as I

> need to use the code elsewhere in my macro i.e ("B5:J5")

>

> Sub CopyRowAndAddAsNewRow()

> Dim StartCell As String

> Dim EndCell As String

> Range("A1").Select

> ActiveCell.Offset(4, 1).Select

> StartCell = ActiveCell

> EndCell = ActiveCell.Offset(0, 8)

> Range("StartCell:EndCell").Select

> Selection.Copy

> End Sub

>

> --

> Kind Regards

>

> Mick

>

them, and wrap the text. The problem I have is the macro

returns to the row on which the macro was recorded. How do

I have the macro select say columns B through F relative

to the row I am on, then perform the rest of the macro.

Thanks in advance!

Kevin

have tried and failed.

I would like to be able to use ctrl+clicking to select non-adjacent

cells in a column, then reuse that set in other formulas for doing

things like averages or sums of columns.

For example, I ctrl click in column B to select cells in rows 1,2,3,

and 5. I want to maybe do a sum in column C for those rows, and maybe

average in column D for those rows.

I tried giving them a name, then doing soemthing like

Sum(Offset(NamedSet,0,1))

But this gives #Value error as soon as Offset is evaluated.

I just want to be able to redefine the set of rows that the other

formulas should operate on, and have that cascade to the other

formulas. Most of them are sum formulas, so even a solution with sum

formulas would be nice.

I was thinking of adding a column of checkboxes and somehow use the

sumif formula, but the checkboxes are kinda funny in the way they

"float" around and don't seem to actually reside inside a cell.

have tried and failed.

I would like to be able to use ctrl+clicking to select non-adjacent

cells in a column, then reuse that set in other formulas for doing

things like averages or sums of columns.

For example, I ctrl click in column B to select cells in rows 1,2,3,

and 5. I want to maybe do a sum in column C for those rows, and maybe

average in column D for those rows.

I tried giving them a name, then doing soemthing like

Sum(Offset(NamedSet,0,1))

But this gives #Value error as soon as Offset is evaluated.

I just want to be able to redefine the set of rows that the other

formulas should operate on, and have that cascade to the other

formulas. Most of them are sum formulas, so even a solution with sum

formulas would be nice.

I was thinking of adding a column of checkboxes and somehow use the

sumif formula, but the checkboxes are kinda funny in the way they

"float" around and don't seem to actually reside inside a cell.

1.sort table -it'easy

2.from sorted table to select a range of cells until a value from sorted

table is matched.This is the problem.

3.copy selected range in another sheet-it's easy.

Thanks,

I'd like to know if the following is possible within Excel VBA and if so how to implement it please?

I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result.

For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z).

The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).

Hopefully I've explained this ok, but if not I'm happy to help with any more info you need.

Thanks!

Thanks.

I was wondering how to select, say, the 5 cells below the ActiveCell.

My initial thought was to use the ActiveCell.Offset method but I'm not sure that would let me select a range of more than one cell.

Thanks in advance!

edit: I'm using 2003

I seem to be going round and round in circles with this, but I'm sure it should be easy.

I'm just trying to select a range of cells in Sheet2 of my workbook.

I've tried many different bits of code, including:

Dim namesTotal As Integer

namesTotal = 2500

Sheets("Sheet2").Activate

ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select

(According to the Microsoft website, this is supposed to be the way to do it?)

Thanks in advance for your help.