Free Microsoft Excel 2013 Quick Reference

VBA: Formulas and list separator compatibility problem

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 need to create a summary worksheet that pulls in 'totals' from several other worksheets in the same workbook.

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

Dear All,

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

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

Thank you for any help offered.

Aidan

Hello,

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.

Hello everyone, I'm quite new to macros & vba and I have a question..
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!

Hi, I'm trying to find an Excel formula or array, etc. to be able to do the following:

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!

I have a table with monthly headings, each column cell contains a forumula to update the figures for the current month, I then need to remove the formulas and keep the value in the column for the current month. Can this be done in VBA?Thanks for your help

I have a form like this (the same form represented in 4 differents laguages)

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

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

While I know this could be accomplished simply using a filter, I'd like to try to add a more user-friendly lookup feature to a sheet. My data consists of a list of names in column D with a list of sports and honors in columns O through CZ. For example:

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!

I have a formula I want VBA to adjust as defined by user and insert into a cell:

=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).

Hi,

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,

hello,
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.

In some countries (like Slovakia, Czech Republic, ...) there is comma (",")
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

I have a list of sales people and sales amounts. I need to build a formula
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 am new in excel VBA programming and now facing a problem is that:

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.

Hi,

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

Hi all,

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

I'm really stumped right now. I do not have any experience with VBA but after searching the web, it seems like this would be my only option in order for me to run a macro. I tried to record a macro while I input my formulas but I guess my formulas are too long. Is it possible to simplify these formulas into VBA so I can run it as a macro. This will be a macro that will be used very often with data ranging from 300-2000 rows.
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)

I need to copy values from cells based on a criteria and past them into a range on a separate sheet. i have names ranges setup for the source (1145 cells in a column) and the target range (single cell to paste down from). I also set a cell with the criteria because I am attempting to use AdvancedFilter. however, now that I been trying, I think that I cannot do this with the advanced filter for a few reasons.

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 sub
Thanks
Robert

Hi all,

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").AutoFit
This 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!

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

Hi all,

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

Dear Expert,

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

Hi,

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