Posts by Batman

    Re: Changing Index Match Lookup Value


    Hi balders,


    Assuming that you're using a relatively recent version of Excel, which supports the IFERROR function, try:


    =IFERROR(INDEX(D:D,MATCH(INDIRECT(G2&ROW()),C:C,0),1),"")


    Hope this helps.

    Re: Find equipment clashes using a formula


    Any reason you don't have a single column for Pack, and enter the number in that column, and one column for Team, and enter the number in that column? It would probably make analysis easier, depending of course on exactly what you are trying to do.

    Re: filter with criteria


    Hi,


    First thing I can see, without looking too closely at your VBA code, is that the range name 'Data_Table_With_Heads' is hard coded with a maximum of 100 rows, although you have 150 rows of data.


    =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$100),COUNTA(Data!$1:$1))


    Code
    'AdvancedFilter data by chosen criteria
                        Range("Data_Table_With_Heads").AdvancedFilter _
                        Action:=xlFilterCopy, CriteriaRange:=Range("FilterCriteria"), CopyToRange:=.Range("Z1")

    Re: Trim PART of a Path.


    If the full path is in A1, and the instance of / to start from is defined as a number in A2, try:


    =RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"/","|",A2))+1)

    Re: Split String with Spaces


    I suspect that the data in the file is delimited by something other than a space.


    Try stopping the program and putting something similar to this in the Immediate Pane:
    ? Mid(Myinfo(0),3,1)=Chr(32)


    If it is a space that should return TRUE. If it's a different non-visible character it will return FALSE.

    Re: Split String with Spaces


    Hi gfabyan,


    Welcome to the Ozgrid forum.


    Almost correct with the code tags, but you need to put square brackets [] around 'code' and '/code'. Try editing your post to add these, and you should see the VBA code appear correctly.

    Re: Que regarding conditional formatting in ms excel


    Hi SIARAM,


    Firstly, highlight all the cells from A2:M2. Then enter the Conditional Formatting formula =$A$1>0.


    When you return to the CF Rules Manager, you should see, in the 'Applies To' box = $A$2:$M$2.


    If you remove the $ signs from the formula (i.e. =A1>0), Excel will apply the formatting as if you were copying a formula across columns. In other words, A2 would be formatted based on the formula =A1>0, but B2 would be formatted based on the formula =B1>0, and so on.

    Re: Highlight Cell color in the Loop


    Hi kumarkr,


    You have a confusion between array variants and ranges. What I assume you are using is array variants, which do not have the properties (e.g. .Interior) of a range. Also, unless you choose a range that starts at cell A1, your row and column references will refer to the wrong parts of the worksheet (row 1 of the array will not necessarily be row 1 of the worksheet, etc.)


    Unless you particularly want to use array variants, it might be easier to use Range variables.

    Re: Countif excluding blank cells


    Hi,


    Excel obviously thinks there is something in the 'blank' cells, even though a test for ="" returns True, as if you select cell A1 and hit Ctrl + down arrow it takes you to the bottom of the column of data. If the 'blank' cells were completely blank it would stop at A14. I assume you have probably imported the data from another system.


    Similarly, if you create this array formula anywhere in Sheet1 it returns zero:
    =SUM(--(ISBLANK(A2:A43)))


    If you go into the three 'blank' cells and press Delete, the result of your formula changes.


    This seems to work without clearing the cells:
    =SUMPRODUCT(--(Sheet1!D:D=Sheet2!A2),--(Sheet1!A:A<>""))


    Hope this helps.

    Re: Automatic update month when date change based on certain parameters


    Hi,


    The formula can be changed, but I deliberately created it that way for a particular reason.


    My assumption was that, if the billing dates changed by month, then the chances are that they will also change by year, although that may not be correct. I also assumed that your data table would contain expiry dates spanning years. The use of Excel 'Tables' allows you to just add new items onto the end of the table, causing the table to expand with no need to change the formulas that feed off of it.


    Consequently if, at the start of a year you just add to the table the billing dates for the year then there should be no need to delete any data or change any formulas. You could set up the billing dates for several years in advance.


    However, if your billing dates are the same every year, and you would prefer to have calculations as in your last post, please see the attached. The billing dates in the table all have a year of 2000, and the formatting of the cells is changed to not display the year. The calculation in the main table creates a date, to match to the billing dates, of the day and month of the expiry date plus a hard coded year of 2000.

    Re: Trying to Diagnose a Memory Problem


    Your code is making an assumption that the worksheet you want to use is the active sheet in Excel. Consider whether it is possible that you are somehow running the macro when the wrong worksheet is active.


    I suspect that, if you identify the worksheet to a worksheet variable in VBA, and avoid using the Select and Activate methods, your memory problem will go away.

    Re: Convert String to Decimal in Userform Textbox


    The change of cell display format should not make any difference. The underlying value will still be the same, in a number format. The only difference will be in the way that the number is displayed to the screen.

    Re: Convert String to Decimal in Userform Textbox


    Hi,


    What is the value in the worksheet, before you attempt to do anything to it with your macro? And is it stored in the worksheet as a numeric value, or text?


    Whatever you do to the 'number', irrespective of how it is displayed, in a textbox it is always going to be stored as text.


    Without seeing your data, you could try something like:

    Code
    Me.PBUT101.Value = Format(Ques.Range("L151").Value * 100, "0.00") & "%"


    This assumes that your original data is stored in Excel in number format.

    Re: excel vb countifs


    Hi,


    To all involved in this thread, if you are quoting VBA code, please use code tags. I have edited the earlier posts to add them.


    As a pointer, in case it helps, the easiest way to find the R1C1 format of a formula for VBA purposes is to write the formula manually in Excel, in the normal A1 format, then go into the Visual Basic Editor, Immediate Pane, and type:

    Code
    ? Activecell.FormulaR1C1


    and hit return. This will give you the formula you have just typed in R1C1 format. You might need to edit it for use in VBA by adding the extra " characters to return " in the formula.

    Re: Vba to count occurrence if it appears more than 3 times


    Hi,


    It's a little confusing, as you say that column B contains unique identifiers, but then say that you want to count how many times a particular identifier appears in column B. If they were unique then the answer would be 1.


    Do you actually need VBA to do this? It can be done relatively simply with standard Excel functionality.


    Use Advanced Filter to create a list of the unique entries in column B (use 'copy to another location' elsewhere in the worksheet), and then against each item in the new list place a COUNTIF formula. I have set the destination for the Advanced Filter as $D$1, so that the unique list starts at D2, in which case the formula, filled down to the end of the new range, would be:


    =COUNTIF($B$2:$B$36,D2)>=3


    Hope this helps.

    Re: Code / formula to populate data based on Name date and given time.


    Hi Rex933,


    Try this formula in cell B5, filled across and down:


    =IF(SUMPRODUCT(--(TRIM(InputSheet!$B$7:$B$30)=LEFT($A5,FIND(" ",$A5)-1)),--(InputSheet!$A$7:$A$30=$N$1),--(InputSheet!$D$7:$D$30<=B$4),--(InputSheet!$E$7:$E$30>=B$4))>0,"X","")


    The TRIM function is needed as the Participant names in column B of InputSheet all have a trailing space.


    Hope this helps.

    Re: VBA AutoFilter and copy visible data


    Hi chloe29,


    Welcome to the Ozgrid forum.


    Try removing the = sign from the filter criteria. At the moment it's expecting the = sign to be part of the string you are filtering on. E.g.:

    Code
    SourceSQ01FilterRng.AutoFilter Field:=4, Criteria1:="Pkt"


    Hope that helps.