Hi,

I'm having problems with the list separator compatibility...

First I check the list separator of the user using Application.International(xlListSeparator) and I have no doubt he's using ";"... Then when it comes to a validation procedure like the one below it returns me a "Run-time error '1004' --> Application-defined or object-defined error".

HEre is the code:

Range("A:A,C:C,E:E,G:G,I:I,J:J").Select

With Selection.Validation

.Delete

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _

Formula1:="=IF(LEFT($A1;1)=""$"";TRUE;IF(AND(LEN($B1)=0;LEN($C1)=0;LEN($D1)=0;LEN($E1)=0;LEN($F1)=0;LEN($G1)=0;LEN($H1)=0);TRUE;IF(LEN(A1)>8;FALSE; TRUE)))"

.IgnoreBlank = False

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = "INPUT ERROR"

.InputMessage = ""

.ErrorMessage = "You can't have more than 8 columns in this field!"

.ShowInput = False

.ShowError = True

End With

When I try the same code with commas as the list separator in a system configured to use commas, I don't get the error.

Can anyone see what am I missing here???

thanks

I'm having problems with the list separator compatibility...

First I check the list separator of the user using Application.International(xlListSeparator) and I have no doubt he's using ";"... Then when it comes to a validation procedure like the one below it returns me a "Run-time error '1004' --> Application-defined or object-defined error".

HEre is the code:

Range("A:A,C:C,E:E,G:G,I:I,J:J").Select

With Selection.Validation

.Delete

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _

Formula1:="=IF(LEFT($A1;1)=""$"";TRUE;IF(AND(LEN($B1)=0;LEN($C1)=0;LEN($D1)=0;LEN($E1)=0;LEN($F1)=0;LEN($G1)=0;LEN($H1)=0);TRUE;IF(LEN(A1)>8;FALSE; TRUE)))"

.IgnoreBlank = False

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = "INPUT ERROR"

.InputMessage = ""

.ErrorMessage = "You can't have more than 8 columns in this field!"

.ShowInput = False

.ShowError = True

End With

When I try the same code with commas as the list separator in a system configured to use commas, I don't get the error.

Can anyone see what am I missing here???

thanks

- List specific worksheet and use in VBA formula
- Excel VBA Compile error: Expected: List separator or )
- Table to List Data Formatting Problem.
- let macro/VBA make its own formula and execute it
- Excel formula to list keywords found in cell text content.
- Remove formulas and keep values in a table using VBA
- Macro(VBA) for modifiable list
- Cell references in VBA formulas
- Lookup and list all populated cells...
- VBA, formulas and variables
- Vba if...and...statement
- Formula to list the first and the last day of each month
- Regional settings independent list separator in arrays
- Formula to sum and list highest to lowest
- Is it possible to open the VBA form with a link in a sheet and to pass variable from a cell to the VBA form?
- Problem with Array Formulas and ISNUMBER
- Identify and list all formulas
- VBA for Formula and conditional formatting
- Copy and paste using AdvancedFilter problems
- Formulas and numberformatting do not work on VBA generated sheet
- VBA Formula and Loop
- Cross Post >> Use Vba Variables In Formula And Function
- Formula and path problem
- Need help with a small formula and referencing cells

The summary sheet is Sheet9, tab name Complete TC Summary

Totals will be created from various columns in each Results sheets

These Result sheets have identical column structures, just containing different data and worksheet names

There could be many Result sheets that include, and come after (to the right of), the Current TestCycle Results worksheet

No other sheets apart from Result sheets come after the Current TestCycle Results worksheet

Requirements:

From A4 of the Current TestCycle Results needs to list all worksheet names of the Results sheets (no other sheets)

Adjacent to each worksheet name will be a count of items from that particular worksheet. For example in A5 will be counting all the words 'Fail' in column P from every listed worksheet, in column N & O a sum totals durations and returns the value for every listed worksheet

I have attempted this without any joy. Cant seem to only list specific worksheet names although ws. could be used in a VBA formula. File attached.

Any help or tips greatly appreciated :-)

My overall code is to, on click into a cell in a calendar made in excel, output some data into a cell named LiveOutput. To do this I am trying to convert a conditional formatting rule into a piece of VBA in order to formulate the elseif statement. The code (which is incomplete for the complete task) can be seen below.

VB:When trying to compile the elseif line, I am receiving the error message "Compile error: Expected: List separator or )" at the first ""=Y"", specifically highlighting the 2nd "". This line has been modified from writing a macro with the macro recorder to capture the formula. This part has not been adapted from this so I am at a bit of a loss as to why it is not working and how I can get past this point.Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim LiveCalendar As Range Dim LiveYear As String Dim LiveBusArea As String Dim LiveOutput As String Dim LiveStart As Range Dim LiveEnd As Range Dim LiveLength As Range LiveYear = ThisWorkbook.Names("LiveYear").RefersTo LiveYear = Mid(LiveYear, 3, Len(LiveYear) - 3) LiveBusArea = ThisWorkbook.Names("LiveBusArea").RefersTo LiveBusArea = Mid(LiveBusArea, 3, Len(LiveBusArea) - 3) LiveOutput = ThisWorkbook.Names("LiveOutput").RefersTo LiveOutput = Mid(LiveOutput, 3, Len(LiveOutput) - 3) Set LiveCalendar = ThisWorkbook.Names("LiveCalendar").RefersToRange Set LiveStart = ThisWorkbook.Names("LiveStart").RefersToRange Set LiveEnd = ThisWorkbook.Names("LiveEnd").RefersToRange Set LiveLength = ThisWorkbook.Names("LiveLength").RefersToRange With ActiveWindow If ActiveCell.Value > 0 And IsNumeric(ActiveCell.Value) = True And InRange(ActiveCell, LiveCalendar) Then ' Yes, We are in the calendar and it is a number over 1 Range("LiveOutput").Value = ActiveCell.Value ElseIf Application.IsText(ActiveCell.Value) = True And InRange(ActiveCell, LiveCalendar) And (Application.WorksheetFunction.CountIfs(Indirect(LiveBusArea, ""=Y"" ,LiveStart,DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2))) + Application.WorksheetFunction.CountIfs(INDIRECT(LiveBusArea),""=Y"",LiveEnd,DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2)),LiveLength,"">1"") + COUNTIFS(INDIRECT(LiveBusArea,""=Y"",LiveEnd,"">"" & DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2)),LiveStart,""0" Then 'Within range but day of the week Range("LiveOutput").Value = ActiveCell.Value Else 'Blank so make the output box blank Range("LiveOutput").Value = "" End If End With End Sub Function InRange(Range1 As Range, Range2 As Range) As Boolean ' returns True if Range1 is within Range2 Dim InterSectRange As Range Set InterSectRange = Application.Intersect(Range1, Range2) InRange = Not InterSectRange Is Nothing Set InterSectRange = Nothing End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you for any help offered.

Aidan

I’m trying to convert several fairly small tables into lists using formulas but am having a problem with the number/text format resorting to default, when it’s made into the list. The list part is working, it’s just the formatting that changes.

I've attached an example file.

The data in the tables could be currency, dates, percentages or any other format and I need them to be copied into the list’s cells with the same number/text format appearance. Eg, If D11 has the date 21/12/2010, and in O17 I then enter =(D11), O17 will also have the 21/12/2010 date.

As I am referencing cells indirectly, I think it’s this that’s causing me the problem. At the moment I’m using the INDIRECT function but have also experimented with INDEX. Both haven’t formatted the data correctly.

My tables’ column/row size varies but I input each table size in 2 separate cells to make cell referencing a little easier (eg Rows 9, Columns 5) and the tables always start at B10. Doing it this way, I can get the formula list to count up for the columns & rows.

I then use these column and row numbers to reference the cells in the table.

The other problem with the INDEX function is that I don’t know how to input the range as Column numbers and Row numbers.

Has anyone got any ideas how I could keep the formatting?

Also and although it still wouldn’t format, how would I enter the range in the INDEX function using Row numbers and Column numbers?

Thanks.

My excel sheet looks like this:

From Till #Workdays Month #Workdays

1 2 20 1 ?

3 7 24 2 ?

8 24 21 3 ? (this goes till 24)

From is a column indicating the starting month

Till is a column indicating the end of the period

#Workdays is a column

Month is a column indicating a specific month (this goes from 1-24)

and the last column should be defined by the macro by the previous information

What will happen is that the "from", "till" and "#workdays" column will be filled in manually and then the macro/vba should define the the number of workdays in that specific month...

so the formula looks like this: (with A3 being the first from number)

=IF(AND($G$3<=J3;J3<=$H$3);$I$3;IF(AND($G$4<=J3;J3<=$H$4);$I$4;IF(AND($G$5<=J3;J3<=$H$5);$I$5;IF(AND ($G$6<=J3;J3<=$H$6);$I$6;"?"))))

the problem is that the "From", 'Till" and "#Workdays" column are not limited to 3 periods, it could also be 4,6 or... So the formula needs to be adjusted depending on the number of periods, is there any way vba or a macro could make this formula and than define the number of workdays for that specific month?

Thanks in advance!

I have a column in a spreadsheet with about 50 text keywords/phrases (one keyword/phrase on each row) and I need to find which rows of article text contain one or more of these keywords. Then I have rows in the spreadsheet that contain the text content for articles with the following column headings:

Column A is the keyword list

Column B is the Article Title

Column C is the Author's Name

Column D is the Article Text

Column E is the Article Publication Date.

Each row in the spreadsheet contains the content for one article so I need the formula to check to see how many (if any) of the column A list of keywords exist in the article text cell of each row (column D). Also, if possible I would like the formula to tell me how many times each keyword exists in each article text.

So for example, if I have 1,000 rows articles about real estate in the spreadsheet and I have a list of keywords like:

mortgage loan, kitchen, realtor, house appraiser, plumbing, electrical

If an article contains the word "mortgage loan" twice, "house appraiser" once, and "electrical" three times, then I would like the formula result to indicate which keywords it found in that article, and if possible how many times it found each keyword in that article. (To calcluate each row separately I could use the fill-down command to copy the formula in a column to calculate this for each row of the 1,000 articles).

If possible, I would like the formula to list the result from highest keyword frequency to the lowest. So in this example, the result would be listed as:

electrical 3, mortgage loan 2, house appraiser 1 (if the keyword frequency amount can be listed in the result)

or else list the result as:

electrical, mortgage loan, house appraiser

Thank you very much for any advice you have!

Name

Company code

Account ID

etc

People will have to type data in Name or Account ID fields but

Company code will be a list , one just needs to click to chose the right code. (modifiable list provided on excel, sorry, since I use Excel in french, so I can not give the exact name of this kind of list)

I have created a VBA and asssociated to 4 differents buttons which represents 4 differrent languages. Once one button is chosen, the form will appear in its language. My problems is how to change the company code list in the same language of the button. Of course I have 4 lists in 4 differents languages but I dont know how to associate them to the VBA

Hope I have made my problem clear. Thanks a lot for any help

I'm frustrated.

I get that there are many ways to reference cell addresses(A1, RC, offset from an active cell, etc.). But when to use which

Putting one of these into a VBA formula I don't get. I'm almost ebarassed to even broach the subject to such knowledgable people

Okay, after looking through an awful lot of posts, what does the "!" mean in the following: !R1C1:R44C5 Is it an absolute vs relative indicator?

I'm working on a SUM(range) formula but although Excel macro words can be used in VBA, I can't find an explaination in VBA Help. Its index stops at "Submit". The VBA help lists it as a worksheet function but not how to use in a VBA module.

I NEED TO READ - Please point me in some direction (online hopefully).

I note the "Cells" property and it has the column, a comma, and the row. That would seem ideal to use (a variable for the row))

This is what I'm attempting in a very crude manner

Qty=sum(cell($c, $row) to (cell($c, row variable)))

I've dim'd the variables. Running it, the "sum" is highlighted and the error is "Sub or Fuction not defined"

While I'd like the answer, I'd also like to be pointed to a source for putting things together.

Hope my rambling has at least lead to a and that I have not taken up too much forum space and your time. Please don't kick me out. I'm trying! At 58 some of the brain functions may be gone.

John

D11: Joe Smith

O10:T10 (merged): Basketball

O11: Years Played

P11: Years Managed

Q11: Years Captained

R11: Years All Conference

S11: Years All County

T11: Years All Metro

U10:Z10 (merged): Badminton

U11: Years Played

etc.

What I'd like to do is utilize a combo box in H4 to pull up a name (I'd love to use autocomplete within this box but I know that's entirely another question) and have a formula yank the appropriate data and list only the sports played, how many years, etc. Something like this:

H4: Smith, Joe

I4: 4 Years Basketball

J4: 2 Years All Conference

I5: 2 Years Baseball

J5: 2 Years Manager

I6: 3 Years Lacrosse

J6: 2 Years All Conference

K6: 1 Year All Metro

Is there an easy way to do this? This sheet is already calculation-full, so much so that I've switched to manual calculations. Would it be easier to utilize a user-form? I'd need a lot of help creating that as well as I'm a complete rookie when it comes to VBA. Formulas I can work with; VBA... not so much.

Hope that all makes sense. Please excuse any stupid questions. Any help is very much appreciated!

=Vlookup( *FirstVariable* ; Indirect(" *SecondVariable* .xls!_" & *ThirdVariable* ;10;False)

So if the user inputs B1, Filename, 345 the result in activecell should be

=Vlookup(B1; Indirect("Filename.xls!_" &345;10;False)

How do I make it happen in VBA language?

Been trying all morning but it keeps giving me the error 1004

What's funny is that a msgbox gives the proper result, and that the makro works if I remove the first equalsign (basically making it a text input not a formula).

How do I write a vba "if-and" statement?

For formula, I have

=IF(AND(ISTEXT(A1),ISBLANK(B1)),"3","")

If A1=text AND B1=blank, then put "3" in C1

next cell if A2, and so on... to the end of the list

Thank you,

is there a formula to list the first and the last day of each month and for every year?.

example:

if i put in cell A1: 01/09/06 ----->01/September/2006

the result should be:

A2: 30/09/06 ------> 30/September/2006

A3: 01/10/06

A4: 31/10/06

A5: 01/11/06

A6: 30/11/06

A7: 01/12/06

A8: 31/12/06

A9: 01/01/07

A10: 31/01/07

A11: 01/02/07

A12: 28/02/07

.

.

.

and so on

how to do that with a formula?

thnak you so much for your help.

used for decimal symbol and semicolon (";") for list separator.

And that is my problem - in "standard US settings" you can:

- insert horizontal array into range of cells with comma (={1,2,3,4,5} and

press Ctrl+Shift+Enter)

- insert vertical array into range of cells with semicolon (={1;2;3;4;5} and

press Ctrl+Shift+Enter).

So - is there any way to work with those arrays in "regional settings

independent" way ?

Thans to all hints in advance !

Vlado

that will give me each persons total sales and list them from highest to

lowest. How would I build a formula with the data below?

Steve $1000

Steve $1500

Peter $800

Peter $500

Jane $1700

I have a worksheet containing the company details and each company

contain a unique ID. I have create a VBA form to list the detail of a

specified company using their company ID (I am now hardcode the ID in

the form at this moment). I want to allow users to open the VBA form

to see the company detail by clicking the company ID in the worksheet.

But I do not know how the form link with the company ID in the

worksheet.

Is it possible to call the VBA form with a link? In addition, is it

possible to pass the ID from the worksheet to the form? Or any other

solutions.

Thanks for helping.

I have encountered the following problem with array formulas, which is

described below (after the introduction that sets up an example).

Let's imagine a dataset where columns B through E represents years 2000

through 2003 (which are entered into row 1) and rows 2, 3, 4 and 5 represents

North, South, East, and West (which are entered into column A) so that the

data that populates the table covers B2:E5. Let's further imagine that cell

D5 is left blank. All values are positive integers.

Using Boolean logic and an array function, I can pickup the cross section of

East and 2002 by submitting the array formula

{=SUM(B2:E5*(2002=B1:E1)*("east"=A2:A5))}. Furthermore, I can count the

number of observations in East and West by submitting

{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2 :A5)))}, which returns 7

(keeping in mind that D5 is blank). Now, if I want to find the sum of east

and west, I would substitute "1" with B2:E5, so that my array formula looks

like {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east "=A2:A5)))} (which

with my unique dataset returns 64). I am aware that the ISNUMBER term is

redundant in calculation example.

Now, lets say that rather than being blank, cell D5 contains a formula that

returns "", such as ="". If I want to count the number of numerical

observations in East and West, I can still enter

{=SUM(1*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east"=A2 :A5)))}, which returns 7.

However, and this is the problem, if I want to find the sum of East and West

and enter {=SUM(B2:E5*ISNUMBER(B2:E5)*(("west"=A2:A5)+("east "=A2:A5)))},

Excel returns an error (#VALUE!). This problem has been bugging me for a

while and I would really appreciate a solution. The solution should not be to

change the contents of cell D5 or to simply take SUM(B4:E5). Usually I

wouldn't have entered "west" and "east" into the formula, but these would

actually reference some other input. It is important that the sum calculation

would dynamically update if, for instance, "east" was changed to "south". I

hope this makes sense and I appreciate any suggestions.

Thanks,

Henrik

I have a large spreadsheet that is full of complicated formulas and named ranges.

Is is possible to scan the workbook and produce a list of all cells that have a formula or named range?

eg. the list would show

cell formula named range

A1 =MyRange+B2 MyRange

or something similar? Has anyone designed an Add-in/Utility that would do this?

Many thanks in advance

Here are my formulas and conditional formatting.

In column AA1:

=IF(AND(ISBLANK(J1),ISBLANK(K1)),FALSE,IF(J1=1,ISERROR(MATCH(K1,Sheet1!$K$3:$K$1000,0)),IF(J1=2,ISERROR(MATCH(K1,Sheet1!$L$3:$L$1000,0)),IF(J1=3,ISERROR(MATCH(K1,Sheet1!$M$3:$M$1000,0)),IF(J1>=3,ISERROR(MATCH(K1,Sheet1!$O$3:$O$100,0)))))))

In column AB1:

=IF(AND(OR($J1=1, $J1=2), OR(ISBLANK($L1), $L1="other", $L1="done") ), TRUE, FALSE)

In column AC1:

=IF(AND(OR($J1=1, $J1=2), ISBLANK($M1)), TRUE, FALSE)

In column AD1:

=IF(AND(OR($J1=1, $J1=2), ISBLANK($N1)), TRUE, FALSE)

Column J (conditional formatting):

=$AA1

Column K (conditional formatting):

=$AA1

Column L (conditional formatting):

=$AB1

Column M (conditional formatting):

=OR(ISNUMBER(M1),$AC1,$AD1)

Column N (conditional formatting):

=OR(ISNUMBER(M1),$AC1,$AD1)

My source range cells contain formulas and the formula is what gets pasted rather than the values.

The source cells contain the word "OK" or values like "T6-08", "T1-52" etc. i need to copy the TX-XX values and not the OKs. I tried to use <> but apparently I'm limited to plan old "="

For some reason only one cell in the target ranges gets populated even though there are about 1150 cells that would qualify as I have it now (criteria = "OK".

Before I give up on using advancedfilter can someone tell whether or not I can do this.

I would be grateful for an example of another method to a similar problem.

Public Sub ImportData() Dim srcRng As Range Dim xdBad As Range Dim tgtRng As Range Dim myCrit As Range Set srcRng = Range("ipHealthSht") '1145 cells Set tgtRng = Sheets("FailureAnalysis").Range("a2") Set myCrit = Range("FAcrit") ' contains the text "OK" srcRng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=myCrit, CopyToRange:=tgtRng, Unique:=False end subThanks

Robert

I have filled a worksheet with values with a VBA program. It reads serveral values from autocad and places it in the worksheet using to following statements:

ATTRIBUTES.Activate 'activate and clear ATTRIBUTES worksheet ATTRIBUTES.Range("A:IV").Clear ATTRIBUTES.Range("A:IV").NumberFormat = "@" ATTRIBUTES.Range(BLOCK_COUNT + 1 & ":" & BLOCK_COUNT + 1 + UBound(BLOCKS, 2)) = Application.WorksheetFunction.Transpose(BLOCKS) ATTRIBUTES.Columns("A:IV").AutoFitThis works perfectly, I get a neat table which is formatted as text.

However formulas and manual numberformatting doesn't work anymore on those values

For example the MAXA formula doesn't work (which should work with values formatted as text)

and setting the numberformat manually to numeric doesn't work either.

when I create a new worksheet and type and format the values manually, everything works perfectly, so whats going wrong with VBA?

Thanks in advance for your help!

I need to do a loop down column B, starting at B6, for as long as there is data in column A.

For each cell in column b, I want to enter this formula:

I'm not sure how to write the formula in VBA and I haven't quite got the loop working either.

Thank you for any assistance.

Karin

My question is that how i can put variables in excel formula and store the results of the formula using VBA.

Say, i need to build a function to do sumproduct. I have a variable to store the file path like vFilePath. I don't know how to put it in VBA in the formula below. And i need to store the result of the formula so that i can later use it for other calculation. I need to replace the file path first store the result as a variable. E.g.

=IF($A9="","",SUMPRODUCT((LEFT('C:temp[testlog_BIL(COM).xls]Sheet1'!$C$2:$C$30,8)=$A9)*('C:temp[testlog_BIL(COM).xls]Sheet1'!$D$2:$D$30=B$1)))

Is there any possible way?

Thanks

I have a problem in Excel formula.

I have a formula and when I type in or copy a path to it and then press return, the copied path wasn't showed in the formula. So I can't get the number in the file of that path.

The original formula:

=SUM(IF(('[ABCDE - Worksheet - Sep 07.xls]Sep 07'!$G$1:$G$1500=$D2)*('[ABCDE - Worksheet - Sep 07.xls]Sep 07'!$M$1:$M$1500=$F$11),'[ABCDE - Worksheet - Sep 07.xls]Sep 07'!$I$1:$J$1500))

Now I want to add to

P:AAAAAXXXX20062006 CAR in front of the path [ABCDE - Worksheet - Sep 07.xls]. So the path should be P:AAAAAXXXX20062006 CAR[ABCDE - Worksheet - Sep 07.xls]

However, when I typed to it, the forumula returned to the original one.

Is any expert know the reasons and solutions?

Please advise.

Thanks

I need some help with formulas and referencing cells in Excel. I've used Excel before for simple tasks like finding the sum of number etc. but I've never used it for anything more complicated than that.

I've attached the file I'm working on.

In there, is a worksheet called "Clients", and a worksheet called "Form". (I'll format the form later Sheet "Clients" contains all the data, and sheet "Form" is where all the referencing happens.

I've created a dropdown list in sheet "Form", that takes its data from sheet "Clients". When i select "Business A" in sheet "Form", all data related to "Business A' which is stored in a row in sheet "Clients", needs to appear in sheet "Form".

My problem is, I've managed to do it for "Business A", but when I choose "Business B" how do I tell Excel to look for data in cells B3 C3 D3 and not B2 C2 D2. Its hard to explain here.

I'm sure you'll understand what i'm trying to do if you looked at the Excel file and selected Business B and Business C from the dropdown list. There might be some method like using * (wild cards) to reference cell numbers.

I did a formula =IF(B1="Business A",Clients!C2,"Error") with a lot of difficulty.

Also, is it possible to use an easier reference instead of "Business A" "Business B" in the formula. (Only in the formula and not in the drop down list)

Thanks

Norman