Posts by Binning

Re: Custom function to assign value to text string

Sorry XOR, it works fine now. Thanks so much for your help.

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.

Re: Custom function to assign value to text string

XOR LX, you're a genius. Thanks so much for this.

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.

i.e.

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.

EDIT:

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

Thanks

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:

Sub MyDate()


Dim MyDate as Date


MyDate = Date - 8
Range("J1", Range("J" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=">" & MyDate


End Sub

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'

Roy,

This code is part of a bigger macro. I just took this snippet out as an example. Thanks for your help.

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)

Dim week1 As Double, week2 As Double
Dim week3 As Double, week4 As Double
Dim rng As Range
Dim lBottom As Range


Range("K2").Select
Set rng = Range(Selection, Selection.End(xlDown))
week1 = Application.WorksheetFunction.CountIf(rng, "1 week")
week2 = Application.WorksheetFunction.CountIf(rng, "2 weeks")
week3 = Application.WorksheetFunction.CountIf(rng, "3 weeks")
week4 = Application.WorksheetFunction.CountIf(rng, "4 weeks")


Sheets("Sheet5").Select
Range("A2").Select
Set lBottom = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
lBottom.Select
With ActiveCell
    .FormulaR1C1 = week1
    .Offset(-1, -1).FormulaR1C1 = week2
    .Offset(-2, -2).FormulaR1C1 = week3
    .Offset(-3, -3).FormulaR1C1 = week4
End With

Any idea why this is happening?

Re: Change data source on pivot table

Hi there,

I've realised its because my headings only go up to AX as opposed to AY. It seems to work fine now.

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:

Sub ChangeDataArea()


Dim shNew As String


shNew = Sheets(Sheets.Count).Name


Sheets("Sheet2").PivotTables("PivotTable3").PivotCache.SourceData = _
        shNew & "!" & Range("A1:AY2000").Address(ReferenceStyle:=xlR1C1)


End Sub

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

This worked perfect, thanks Luke

Re: Extract date from filename for new tab

Luke,

I don't suppose you could give me a similar code for naming a tab after this week's start date (the monday of the week) in the same format?

Thanks,
Binning

Re: Extract date from filename for new tab

Luke I could kiss ya.

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)
    Sheets(Sheets.Count).Select
    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?

Thanks,
Binning

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:

Sub EqualRanges()
    Dim wb As Workbook
    Dim ws1 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets("OBOB.NGP_Data")
    Set rng1 = ws1.Range("A1:AL1")
    Set rng2 = ws1.Range("A2:AL2")
    If rangesAreEqual(rng1, rng2, ws1) Then
        
    Else
        MsgBox "Headings are not equal. Please make changes manually."
        Exit Sub
    End If
End Sub

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

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

HaHoBe is there an issue with posting the same question on a different forum? There are a number of moderators on each who could help me here.