Posts by Binning

    Re: Custom function to assign value to text string

    The only way I could see this working would be to change text to columns with the delimiter as "|" and then do the same function in 3 seperate colums, selecting the max. However this is a lot lengthier than I would have prefered.

    Any suggestions?

    Re: Custom function to assign value to text string

    XOR, this works but only to an extent. It doesn't select the highest risk function. For instance, if I have:

    Red 2
    Green 1
    Blue 0.5

    and then I have RedGreenBlue, it seems to only return the value of blue (0.5). This is a problem as in this circumstance I would need it to return 2.

    I understand the complexity involved in creating a function that does this but any help would be much appreciated.

    Every day we download a report of all the applications the users have access to and the risk ratings attached to them. The risk ratings will be a value ranging from 0 - 2. The risk rating is calculated by all the functions the application allows the user to perform. The problem arises because the report that we download has all the functions in the same cell with no delimiter.

    To make things a little simpler, I need to follow the following logic:

    1. Assign values to all of the possible functions the user can perform (in this instance, we'll just call it Add, Modify and Delete. So Add = 0.5, Modify = 1, Delete = 2).
    2. Choose the highest value and return this value.


    If cell A2 contains the string AddModifyDelete, the function will look like =riskrating(A2) and return a value of 2. At the same time, if the cell had AddModify, it would return a value of 1.

    I've only listed Add, Modify and Delete for simplicity, in reality there are a number of functions, all of which need to be assigned values.


    if it's of any help there is actually a delimiter " | ". I don't know if this makes a difference though. It may also be worth adding that sometimes characters can follow each of the risk factors. For instance it could be Add:AC|Modify|Delete:ACDU


    Re: Problem with date filter

    I've solved it by storing MyDate - 7 as a Date and then storing the date as a number, and then filtering on the number. This seems to be doing the trick. Seems like an excel issue I guess.

    Re: Problem with date filter

    I have realised that it's doing this with all months. It's inverting the DD/MM to MM/DD. I think the only reason it worked previously was because I ran it for the 5th of May (05/05).

    I have no idea why this is happening. Can anyone help?

    Hi there,

    As part of a macro I select all values that occurred within the last 7 days and filter out all those values before. The code is as follows:

    However, and I'm curious if this is just a problem on today's date (because it was working so well before). The value that gets assigned to MyDate is 41765. However, on the filter, it filters values not equal to "41795". For some reason MyDate originally gets stored as 06/05/2014, however on the filter it is 05/06/2014.

    Does anyone have any solutions for this problem? If you include Range("A5").FormulaR1C1 = MyDate in between the two lines, it gives the correct date, however the filter is wrong.

    Any ideas?

    Re: Offsetting within 'With'

    Good god this is the stupidest question I've ever asked. Thanks Luke. I'm meaning to go right and then go up, but I've been going left.

    Sometimes I'm just looking too much into things to miss the obvious. Thanks.

    Hi there,

    I'm trying to run the following macro however I keep on getting Run-time error '1004':

    Application-defined or object defined error

    The purpose of the macro is to determine the number of times week 1, weeks 2, weeks 3, and weeks 4 appear in column K. It then displays this information on another sheet in descending diagonal order (see code below)

    Any idea why this is happening?

    Hi there,

    I'm currently trying to change the data source on my pivot table to the last sheet in the workbook. Currently I'm trying:

    However I keep getting Run-time error '1004:'

    The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

    Any suggestions?

    Re: Extract date from filename for new tab

    If it's of any help to anyone I've managed to extract the YYYY-MM-DD using:

    Sub filename()
    Dim wbName As String
    Dim ShortName As String
    Dim dateval As String
    wbName = ActiveWorkbook.Name
    ShortName = Replace(wbName, ".xlsm", "")
    dateval = Right(ShortName, 10)
    End Sub

    Just the second part that I need to do now I guess

    Hi there, this may come across as being slightly confusing.

    Every week we produce a report that has the filename: Master_BER_Pending Revokes Report_YYYY-MM-DD.xlsm

    Some of the information from this workbook needs to be pasted into a new tab on a different workbook. The new tabs naming convention is simply "DD MM". Currently I've got a command prop that asks the user to enter it in manually. See below

    Dim newsh As String
    newsh = InputBox(Prompt:="Enter new tab name", Title:="Tab Name", Default:="e.g. '05 05'")
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = newsh

    The user gets the information for the month and day from the filename of the report that was produced. What I need is the DD and MM to be extracted from the filename and used to name the new sheet.

    Can anyone help?


    Re: If Row 1 = Row 2 then delete row 2, else msgbox

    I've figured this out on my own anyway. Sorry for failing to read the forum rules. For anyone who is interested please see below:

    Function to see if ranges are equal

    Function rangesAreEqual(rng1 As Range, rng2 As Range, _
        ws1 As Worksheet) As Boolean
        If rng1.Columns.Count <> rng2.Columns.Count Then Exit Function
        If rng1.Rows.Count <> rng2.Rows.Count Then Exit Function
        rangesAreEqual = ws1.Evaluate("=AND(EXACT(" & ws1.Name & "!" & _
                rng1.Address & "," & ws1.Name & "!" & rng2.Address & "))")
    End Function