Posts by marykings_25

    Re: Search for any character contained in a given cell on another cell


    Thanks guys!


    In the meantime i found an alternative way of doing what i wanted.


    Kind regards



    Quote from apo;681804

    Hi..


    This is the first function i have made.. but i think it does the job..


    Code
    Public Function Dexist(a As Range, b As Range)
    Dexist = InStr(a, b)
    If Dexist <> 0 Then Dexist = "1"
    If Dexist = 0 Or b = "" Then Dexist = ""
    End Function

    Re: Search for any character contained in a given cell on another cell


    Quote from Robert Mika;681722

    =SUM(--ISNUMBER(SEARCH({"a","b","c"},A1)))
    Confirm Control+Shift+Enter
    Add more values separeted by coma.


    Hi Robert and thank you for your reply!


    I am afraid, however, that i can't do that because the content of my cell varies. I can have "abcdef" or "mothyr" or "a", etc, that's why i was looking for an automatic way of doing it whatever my cell content was.



    Best regards

    Hi guys,


    I am trying to write a function that is able to take any character contained in my original cell (which is filled with a text string) and see if it exists on my destination / lookup cell.


    So far i made some attempts, one of which included the use of a wildcard (don't know the first thing about wildcards, so i may be using it incorrectly)


    It was something of the sort:


    =ISNUMBER(SEARCH("["& original cell & "]";within lookup cell)


    An example to make my explanation a bit less confuse.


    Let's say i have a cell with the text "abcdefg". I want the function to search any of these characters- a, b, c, d, e, f, g - against my lookup cell, which has the text "a". Given that "a" is one of the characters of "abcdefg" the ideal function would return 1. I do not need to know the position of the character, i am only interested in knowing if at least one of the characters exists.

    A regular search function returns #VALUE since "abcdefg" is not written anywhere on my lookup cell, which only has "a", thus it cannot be found.


    Thanks!

    Re: Display MsgBox whenever cell is deleted / changed to empty


    Oh and now if i delete the cell content it goes back to the original value and it also pops up the msg box.


    Re: Display MsgBox whenever cell is deleted / changed to empty


    Hi AAE, thanks for the tip! I am a complete newby to this.


    It is still getting stuck whenever i press the "No" answer. It changes the cell value to its original value but then it triggers a new msg box (perhaps application.undo is also being recognized as changing event?)

    Re: Display MsgBox whenever cell is deleted / changed to empty


    One more cry for help.


    I am now trying to add a msgbox so that the user confirms he/she wants to change the field value and if he answers "No" I want the cell to go back to its original value. I repeated the code for empty cell but this time <> meaning that he changed the cell value for something different than empty.
    Whenever i press the "No" button, i get stuck at it and the msgbox keeps popping up.


    Any ideas?


    Tks!


    Code
    If Not Intersect(Target, Range("C6:C7")) Is Nothing Then
            If Target.Value <> "" Then
                Msg = "Are you sure you wnat to change this field?"
                Ans = MsgBox(Msg, vbYesNo)
                If Ans <> vbYes Then
                    Application.Undo
                End If
             End If
        End If

    Re: Display MsgBox whenever cell is deleted / changed to empty


    After some online research I tried something like this but it is not running automatically.. if i execute the code it does work :s

    Re: Distribution of unique values from dynamic list


    Yes, you're absolutely right, sorry..
    When i try to run the macro i recorded (with Pivot Table) VBA returns the following error
    "Invalid procedure call or argument" and when i debug it, it just highlights the part of the code where the pivot table is being created


    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Table_Income", Version:= _
    xlPivotTableVersion14).CreatePivotTable TableDestination:= _
    "Backup!R9C34", TableName:="PivotTable1", DefaultVersion:= _
    xlPivotTableVersion14

    Re: Distribution of unique values from dynamic list


    Hi. Thanks! Also tried that with a macro (though simply in recording code, as i have no idea how to write it down), my problem is that the countifs function is quite heavy for 300,000 records and my excel was constantly crashing.


    How can i attach a file to the post?

    Hi guys,


    I am new to this so apologies in advance if i make any stupid questions.


    I have a large database (around 300,000 columns) where each entry represents a person and i am trying to map the income distribution of my population (e.g., 5 people have an income of 0, 6 people have an income of 0.1, ..., 1 person has an income of 100,000) according to 2 specific criteria - if they have 0, 1, 2 or 3 kids and if they are younger (or older) than 25y old.
    One of my criterion works as a changing input and whenever i change it so will the eligible cases for me to compute the distribution.
    Since i need the distribution I need to count all cases for unique income values.


    I already have two additional columns with if functions to flag eligible cases.


    I tried recording a macro using a pivot table but it just wasn't working..


    Any ideas?


    Kindest regards,
    Mariana