Hi all

I want to understand the logic of array formulas that how they work. infact

I want to know the crieteria behind making the arrays.

I used some arrays and made some as well (like SUM IF array) but the main

thing which I want to know is the crieteria to make arrays and what Excel

functions we can use in arrays and what not? e.g I have seen mainly SUM and

IF functions which are used in arrays but many other functions do not work

when tried to used in arrays.

I have read many articles including microsoft's articles on array formula,

all article contain some formulas (basic and advanced) but the logic and

basic crieteria to make arrays is not explained.

can you please guide me to make the arrays (basic and with nested functions

as well.)

I want to understand the logic of array formulas that how they work. infact

I want to know the crieteria behind making the arrays.

I used some arrays and made some as well (like SUM IF array) but the main

thing which I want to know is the crieteria to make arrays and what Excel

functions we can use in arrays and what not? e.g I have seen mainly SUM and

IF functions which are used in arrays but many other functions do not work

when tried to used in arrays.

I have read many articles including microsoft's articles on array formula,

all article contain some formulas (basic and advanced) but the logic and

basic crieteria to make arrays is not explained.

can you please guide me to make the arrays (basic and with nested functions

as well.)

- How does AND work in array formulas?
- Array Formula: I need a formula to count disctints like-
- Highlighting a cell to the left and right of ever cell that is currently highlighted?
- Check the length and value of specific columns and comparion check between two sheets
- How to add a a word or number at the front and end of the column
- Add something at the beginning and end of wordings
- Can Array formulas be added?
- Inserting * at the beginning and end of string
- Copy the formatting and content of a range
- How to read the Amount and MinusValues of ErrorBars in a series
- Loops - automate the copy and pasting of each individual cell in this column
- How to locate the Row and Column of an Active Cell
- Is it possible to lock the height and width of a cell?
- Determining the height and width of Print_Area in a closed workbook
- Setting the Height and Width of an Image
- Copy the formatting and content of a range
- Highlight the row and column of the active cell
- Macro to return the height and width of an object in the clipboard
- Where to find documentation which list each of the methods and properties of Excel
- Check the length and value of specific columns and comparion check between two sheets
- Urgent Help Required! Double string macro to remove the 1st & last part of Forumla
- Count 2 criteria using Array formula
- Shifting Nonblank Cells and Adjusting an Array Formula for the Nonblank Cells
- Multi-criteria Counting (array formulas)

I want to count the number of positive numbers in a named range that meet certain criteria, but my formula isn’t working as I expect. In the formulas below, the named range “SaleAmount” contains one text value, 56 positive numbers, and several hundred empty cells.

This array formula returns the value 56, which is correct:

{=COUNT(IF(SaleAmount>0,SaleAmount,FALSE))}

I think the following array formula should also return 56, but the value returned is 1.

{=COUNT(IF(AND(SaleAmount>0),SaleAmount,FALSE))}

Why does this array formula return the value 1 instead of 56?

This array formula should return the value 20, but also returns the value of 1.

=COUNT(IF(AND(SaleAmount>0,SaleAmount<100000),SaleAmount,FALSE))

Why does this array formula return the value 1 instead of 20 ?

Can anyone suggest what I am doing wrong?

Thanks in advance for any explanation.

I need a formula to count disctints like =

SUM(1/COUNTIF(C26:C46,C26:C46) but I need conditionals in the range and

criteria of countif.

I've tried this, but its not working.

=+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)

Thanks

I have a code that works great for me the only thing is i really want it to highlight the cell to the left and right of the one being highlighted.

It's a long code as i did it for every line. (not sure how to use the loop funtion) but here is just line for the code. So i would want Cell C6 and E6 to also be highlighted not just D6 .. the tricky part is there is no value of importance on the cells i'm tring to highlight.

Range("AB6,Y6,V6,S6,P6,M6,J6,G6").Select

Range("G6").Activate

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _

Formula1:="=$D$6"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = True

I attached a photo of what the cells normally look like after this code is ran..

that's Cell 6

the image cell 6 b - is how i want it too look.

any suggestions

1. Validation check_1: Check the length and value of “Customer#/ADU”.

The allowed conditions are listed below. If error conditions are raised show the Error message to the “Result” column line.

Length Value Error Message Customer# 6byte “No check” ERROR:Invalid Customer# ADU 1byte 2/3/7 ERROR:Invalid ADU I have used this snippet but it doesn't seem to work..

VB:2. Validation Check_2: Check the “Data Value”lastrow = Worksheets("Main data").Range("A" & Rows.Count).End(xlUp).Row mx = 6 For i = 9 To lastrow If Len("B" & i) < mx Then Range("G" & i).Value = "ERROR:Invalid Customer#" Enf If Next iIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Check the length of “Data Value” filed. Get the information of correct length of each value from “Length” field of “Table information”. “Blank” is also allowed for exceptionally condition..

If error condition is raised, show the Error message” ERROR: Invalid Data value” to the “Result line” field on “Main data” sheet.

Regards

Sravanthi

Does any one know how to add a word or number at the front and end of the column

example

BAM-022311 BAM-022411 BAM-050200P2 TO add WEBOE in the front

WEBOEBAM-022311 WEBOEBAM-022411 WEBOEBAM-050200P2 WEBOEBAM-051900P2

My next one I need help in is place a a number at the end but have it go up each row

example

WEB00000

WEB00000

WEB00000

TO

WEB000001

WEB000002

WEB000003

WEB000004

and so on.

I have one column of wordings below

Column A wordings

dictionary

book

telephone

computer

mouse

etc

If I want it to add something at the beginning and end of wordings, how do I code the marco?

Column A wordings

((dictionary))

((book))

((telephone))

((computer))

((mouse))

etc

Thank

Some new questions have arose from my learning of this wonderful function of excel.

Can Array formulas be added together ?

={=SUM(IF((DAY(FEB!D$9:D$133)A10),1))}+{=SUM(IF((DAY(MARCH!D$9:D$15)A10),1))}

to make each number have an * at the beginning and end, for barcoding purposes.

it only works with numbers

how can I make a custom number format that puts * at the beginning and end of any data entered?

eg-

ABCDE12345

would become

*ABCDE12345*

Thanks,

Corticus

named ranges. When creating new individual price lists which contain

common ranges from the template, I need to select say range called

"Header" which is several columns wide and several rows high.

I need to find a way to copy & paste the formatting and content of a

range, preserving different row heights and column widths, together

with titles etc.

So far I haven't found a solution. Any help gratefully appreciated.

Thanks in advance.

Kind regards

David

--

davidthegolfer

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

davidthegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31806

View this thread: http://www.excelforum.com/showthread...hreadid=515286

series that has X and Y error bars? I need to change the error bars into

error ellipses but first I need the size of the individual error bars.

The ErrorBar method sets the Amount and MinusValues, but the ErrorBars

property appears to only allow access to what it looks like.

im brand new to macros and im having some difficulty programming what i want done. I would greatly value any help or input someone may have for me.

heres what im trying to do:

1) I have 1 column with over 100+ rows of data and I am trying to automate the copy and pasting of each individual cell in this column, into another cell.

2)Once 1 cell is pasted in this cell - some data t is generated and I would like to copy paste these columns into a new spreedsheet.

I have written the following code for 1 cell which is working perfectly, but I am unsure how to loop it for the remaining 100+.

Sub OneCell()

Range("R10").Select

Selection.Copy

Range("B7").Select

Sheets("YahooOptionsQuotes").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select

Range("C9:C38").Select

Selection.Copy

Sheets("YahooOptionsList").Select

Range("C9:C38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select

Range("D9:D38").Select

Selection.Copy

Sheets("YahooOptionsList").Select

Range("F9:F38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select

Range("G9:G38").Select

Selection.Copy

Sheets("YahooOptionsList").Select

Range("G9:G38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select

Range("H9:H38").Select

Selection.Copy

Sheets("YahooOptionsList").Select

Range("D9:D38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select

Range("I9:I38").Select

Selection.Copy

Sheets("YahooOptionsList").Select

Range("E9:E38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select

Range("J9:J38").Select

Selection.Copy

Sheets("YahooOptionsList").Select

Range("H9:H38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select

Range("K9:K38").Select

Selection.Copy

Sheets("YahooOptionsList").Select

Range("I9:I38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

I could just copy paste this code over and over just changing the rows/columns in the code but i believe this would slow down processing?

plus be extremely time consuming

can anyone advise me on what to do? thanks for the help

Does anybody know how to display the Row and Column of an Active Cell and

display it in another cell.

Regards,

area won't change even if I change the size or amount of text in the box. Is

this possible?

range in a closed workbook using VBA.

Ultimately I want to determine the dimensions of the Print_Area so that

when I import it into a word document I maintain the aspect ratio of

the object.

I would like my image to be set to a "Height" of 6.07 and "Width" of 8.90.

I now you can do this manually when you go in Format Picture and resize the image to these dimesions.

Can this be done with a code?

I need to find a way to copy & paste the formatting and content of a range, preserving different row heights and column widths, together with titles etc.

So far I haven't found a solution. Any help gratefully appreciated.

Thanks in advance.

Kind regards

David

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = xlNone Rows(Target.Row).Interior.ColorIndex = 6 Columns(Target.Column).Interior.ColorIndex = 6 End SubBut this gets rid of all conditional formatting in the active sheet, not ideal. Also, when I delete the code to stop using it the last row and column that was highlighted yellow remains yellow.

I know this might be a bit to ask but can anyone produce VBA code that will full fill my wish of being able to highlight the row and column of the active cell without ruining colour formatting?

Thanks in advance

The reason I want to do this is that I want to paste pictures into textboxes in Word and ensure consistency of appearance. So, I’ll set the width of each textbox to a full text column width, adjust the pasted picture’s width to slightly less than the width of the box (maintaining aspect ratio so it doesn’t distort), then set the height of the textbox to a little bit more than the new height of the picture inside it.

So, in order to set the textbox’s size I’d like to know the dimensions of the picture to go inside it before it’s pasted.

Apologies if this looks more like a Word than Excel question

1. Validation check_1: Check the length and value of “Customer#/ADU”.

The allowed conditions are listed below. If error conditions are raised show the Error message to the “Result” column line.

Column name Length Value Error Message

Customer# 6byte “No check” ERROR:Invalid Customer#

ADU 1byte 2/3/7 ERROR:Invalid ADU

I have used this snippet but it doesn't seem to work..

lastrow = Worksheets("Main data").Range("A" & Rows.Count).End(xlUp).Row mx = 6 For i = 9 To lastrow If Len("B" & i) < mx Then Range("G" & i).Value = "ERROR:Invalid Customer#" Enf If Next i2. Validation Check_2: Check the “Data Value”

Check the length of “Data Value” filed. Get the information of correct length of each value from “Length” field of “Table information”. “Blank” is also allowed for exceptionally condition..

If error condition is raised, show the Error message” ERROR: Invalid Data value” to the “Result line” field on “Main data” sheet.

Regards

Sravanthi

Help urgently required!

I need to amend about 3000 cells on on worksheet to remove the first part of the formula and also the last part of the formula!

I cant just amend it and drag it down as its pulling from 20 different tabs!!!!!

Because I will be removing the (())'s Excel's automatic formula checking keeps kicking in on the normal find and replace (with nothing) and trying to auto-fix it for me.

Is there a way to build a basic macro to remove the desired text from the beginning and end of a formula without it being interrupted by the Excel auto-correction which we have been unable to disable?

Each cell has different contend in the middle as it is an oracle based application to load data which each cells loads with different dimensions which are being pulled from all over the sheet.

The formula is posted below and the part I need to remove are coloured in red!

=IF(G$5="CurFcstBudRate",IF('Fixed Assets'!G99=0,"",HsSetValue('Fixed Assets'!G99,$B$3,"Scenario#"&G$5&";Year#"&$B$5&";Period#"&G$6&";View#<Scenario View>;Entity#"&$B$2&";Value#<Entity Currency>;Account#"&$A100&";ICP#[ICP None];Custom1#"&$B100&";Custom2#[None];Custom3#[None];Custom4#BUManInput")),"")

Please help today if possible as we have a deadline pending!

Eternally Great-full!

Chris!

I am using the following array formula to count the total of 2 different criteria on a separate sheet within the workbook. I have used this method before with success and don't understand why I am coming up with the result of zero this time.

=SUM(('Sheet 2'!F2:F39="A")*('Sheet 2'!F2:F39="B"))

I do hold control + shift + enter

Thanks,

Paula

I think this might require a macro to do what I am wanting.

I have 3 rows of data (1,2,3). Two rows contain basic entered numbers (1,2). The third row (3) contains a calculation based on the numbers in the same column in the other two rows (e.g. B4=B2+B3, C4=C2+C3, etc.)

I am then using an array formula based on all the numbers in row 2 and the calculated numbers in row 3.

My problem is that I have some blank cells in row 2 causing my array formula not to work for the data I have. I need an automated method of eliminating all the blank cells in row 2 along with the corresponding values in rows 1 and 3 above and below the blank cells. I would like for the columns containing non-blank values to shift left eliminating any colums with a blank value.

The second problem deals with the array formula. I am dealing with a large amount of data and I am not always sure how many or which cells in row 2 may be blank. I want my array formula to use all the nonblank cells in rows 2 and their corresponding values in row 3. This means my array formula will need to change to account for how ever many columns of nonblank data I have available. For example, my array formula may calculate for 10 columns of data, but 4 columns have blank cells causing incorrect results. Therefore, I need all the columns containing a balnk value to be eliminated from the array formula. I assume this could be done by automatically shifting all the columns containing full data to the left and automatically adjusting the array formula for the 6 columns of data rather than 10 columns.

I have attached a sample worksheet. I have simplified my exact formulas into this simplified scenario for better understanding, but the situation is the same.

I have tried many different formulas, but I'm a novice at Excel and am need of help. The only familiarity I have with this program comes from the misery I've had with it in the last few days. Below are two of formulas that I have tried (found them via web), but to no avail:

=SUM(IF($E$2:$E$120="1",IF($T$2:$T$120="1",1,0),0))

=SUM(($E$2:$E$413="1")*($T$2:$T$413="1"))

If anyone can help, I would be so super appreciative.

Thanks,

Tiffany

No luck finding an answer? You could always try Google.