Posts by converteds

    Re: Collection of anonymous data - USD $negotiable


    I both made the deposit payment to Ozgrid, and full payment to yourself on verbal confirmation that you had completed, without you having sent me anything.

    That was 24 hours ago, after you had said "I will be out for a couple of hours but wil check e-mails immediately when I get back"

    I expect the courtesy of you at least keeping me in the loop, especially since I let you choose your own price of $50 for this project ,which I immediately accepted.

    Let me know what's happening please.


    Hello chaps/chappesses

    I need to create a spreadsheet that allows people to enter values anonymously for analysis.

    I want respondents to tell me how much commission they are paying which recruitment agency, to recruit their employees.
    (Hence the anonymity. I could ask on email, but no one would be willing to disclose their number if they thought it
    was too expensive, for fear of seeming incompetent enough to get a good deal)

    The output will simply be a list of respondents in the rows, obviously not named, against the columns of the agency names.

    Ripoff Ltd Oligolpoly haha Ltd
    Person A 18% 20%
    Person B 20% 17%

    -At no point is any respondent able to know which person entered which percentage.
    -The total list of respondents, around 60, is known beforehand, but every person might not vote.
    -No one should be able to type over anyone else’s number.
    -No one should be able to see anyone else’s results during input of their number – only I will see all the results, and then
    distribute to respondents (this is to stop people opening up the file and looking at the results without contributing)
    -The file needs to be in a shared space, accessed by, say, a Dropbox link.
    -Only respondents on the list should have access to the file

    This site has been of tremendous help to me in the past, thanks to everyone that makes it so.
    Whatever you think is a fair price, let’s start from there, and can negotiate if necessary
    Apologies for not posting the cost in the title, my aim is to pay a fair price.


    Re: Extract Multiple String from a Cell

    That will get ALL digits out, OP only wants those between the "~" delimiter. Try this

    Re: VBA Macro Code Isna/Vlookup Error

    You're trying to tell VBA to post the value of the active cell to the relevant range if it can't find it in that range. The problem is that if it can't actually find it in that range, it never gets past the "If" test in the first place. The issue is that you're using worksheet formula logic in VBA, but what is returned in the case of an error in a worksheet, isn't the same as what is returned in the case of an error in your VBA code.

    You just need a few simple error handling lines so that VBA can query whether or not a calculation is an error, and if so then act on it

    By the way, you've used VLookup to match a value in a one column range, which works fine. But also have a look at the Match function, which is designed specifically to do that

    Re: Lists - C1 is a list A1 (text) repeated B1 times, then A2 text repeated B2 times,

    Try this..

    Re: VBA Code to make multiple sheets visible

    Given that the number of sheets you need to unhide is always 3 times the number in B1, you could use the following..
    If B1 says 2, it will unhide "Sheet1" to "Sheet6". (By the way if "Sheet1" to "Sheet6" are already unhidden, if you run the macro it will hide them instead)

    Sub UnhideSheets()
    Dim i As Integer
    For i = 1 To Range("B1") * 3
    Sheets("Sheet" & i).Visible = Not Sheets("Sheet" & i).Visible
    Next i
    End Sub

    Re: Grouping similiar data + summing one column

    Given your large dataset it might take a while, but have a go with the following. Assumes your data starts in A1 of Sheet1 and has headers. Outputs to Sheet2

    Re: Filter table with macro

    Not a problem, that's what we're all here for...

    The 9 just tells SUBTOTAL that it needs to SUM the desired range. There are other values for that argument available that could also COUNT, MAX etc.

    The help files should generally be your first port of call. Select a keyword in your VBA module and hit F1 to get further info on it.

    Re: Retrieve the file name if I specify the extension.

    you can open your file as an XML table from Excel...that will output it to Excel in the way you want.

    Go to a worksheet from which you want to open your XML file.
    Go to File, Open
    Choose your XML'll be taken to an "Open XML" dialog.
    Click "as an XML table", for example. You should get what you need

    Re: Retrieve the file name if I specify the extension.

    This should do the trick..

    Re: Retrieve the file name if I specify the extension.

    Assuming your file names are in column A starting from A1, the following outputs the desired string to column B

    Re: Filter table with macro

    Glad it helped :=). I've added some comments in the code above.

    Best thing to do is step through the code in break mode by clicking into the module and pressing F8 to go through it line by line. Hover above the variables with your mouse to see their values.

    Re: Filter table with macro

    Try this

    Assumes your table starts in A1 of Sheet1. Output is to columns A and B of Sheet2. I've included a "FiltActivity" variable for you to change in the code below as desired, but you can point it towards a cell on the worksheet instead if you need.

    Re: Vlookup another Sheet wher Data is in Current Sheet and sholud to be loop to end

    Your VLOOKUP assignment statements need to be amended. The following line is performing a logical test between two cells. You're asking if something is equal to something else and Excel is placing the answer into the cell.."FALSE"

    .Cells(ii, "J").Value = ActiveCell.FormulaR1C1 = "=VLOOKUP(iii,Sheet1!R9C[-9]:R65536C[1],10,0)"

    Change the above to

    .Cells(ii, "J").FormulaR1C1 = "=VLOOKUP(" & iii & ",Sheet1!R9C[-9]:R65536C[1],10,0)"

    Note that in the new line
    1. You're telling Excel what the R1C1 formula will be, not asking it to perform a test "The cell R1C1 style formula is equal"
    2. The VLOOKUP string is constructed so that the variable iii is placed into the cell, not the literal "iii".

    Have a go with that. Once you see the formulas in the cells you'll be able to see where you need to make further changes to the code.

    Re: Find missing number from 0-9

    Array formula entered in B4:B13


    To enter as an array formula, copy the above to cell B4. Highlight cells B4:B12, press F2. Now press CTRL and SHIFT together and then hit ENTER as well to complete the entry

    Re: Creating Custom Filter with VBA

    You could use an advanced filter...

    Sub FilterIt()
    With Sheets("Sheet2")
        .Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=.Range("A1:C2"), CopyToRange:=Sheets("Sheet1").Range("A5"), Unique:=False
    End With
    End Sub

    You need to set up a criteria range first with Advanced Filter. The code assumes that your data starts in A4 of Sheet2, with headers

    A1:C2 of Sheet2 is reserved for the criteria range. The first row A1 to C1 contains the three headings "Team", "Day", "Stadium" respectively.

    Cell A2 contains the formula
    Copy this formula across the two columns for B2 and C2 (this assumes your input cells are in A1:C1 of Sheet1)

    Now run the macro - the data is copied to A5 of Sheet1

    Re: Return Month as text from value based on today

    to increment a specific number of months back or forward from today's date and have the formula reference that increment, you could use


    which would give you Feb if calculated today

    Replace the -1 by -2 and you'd get "Jan", and so on