Posts by converteds

Re: Collection of anonymous data - USD $negotiable

Hi,

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.

Thanks

Hello chaps/chappesses

Requirement
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)


Output
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%
…


Constraints
-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

Cost
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.

Thanks

Re: Extract Multiple String from a Cell

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

Sub test()
    Dim r As Range, NewString As String, m As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "~(\d+)~"
        For Each r In Range("B2", "B3")
        NewString = ""
        If .test(r.Value) Then
            For Each m In .Execute(r)
            NewString = NewString & "," & m.submatches(0)
            Next m
        End If
            NewString = Mid(NewString, 2)
            r(, 2) = NewString
        Next r
    End With
End Sub

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

Sub findmissingitems()
  Dim x
    Sheets("Paste Inventory Count").Select
    Range("c1").Select
        
    Do
 
'//Tell VBA to ignore an error and continue (ie if it can't find the value)    
On Error Resume Next


'//Assign the result of your calculation to a variable that VBA can query       
x = WorksheetFunction.VLookup(ActiveCell.Value, _
        Sheets("SKU Reference").Range("A:A"), 1, False)


'//if Vlookup throws up an error, eg can't find the value, then paste it into the required column         
If Err <> 0 Then
            Rows(ActiveCell.Row).Select
            Selection.Copy
            Sheets("SKU Reference").Select
            LastRowColA = Range("A65536").End(xlUp).Row
            Selection.Paste
        Else
            ActiveCell.Offset(1, 0).Range("A1").Select
        End If
        
'//resets to normal error handling
On Error GoTo 0
    Loop Until IsEmpty(ActiveCell)
         
End Sub

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..

Sub SplitIt()
Dim o, n(), SplitString, w As Long, x As Long, y As Long
o = Range("A1").CurrentRegion
x = WorksheetFunction.Sum(WorksheetFunction.Index(o, 0, 2))
ReDim n(1 To x)
y = 1
For t = 1 To UBound(o, 1)
SplitString = Split(RTrim(WorksheetFunction.Rept(o(t, 1) & " ", o(t, 2))))
For w = 0 To UBound(SplitString)
n(w + y) = SplitString(w)
Next w
y = y + o(t, 2)
Next t
Range("C1").Resize(x) = Application.Transpose(n)
End Sub

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

Sub FilterIt()
Dim ConcatCode As Range
Application.ScreenUpdating = False




With Sheets("Sheet1").Range("E1").Resize(Range("A1").CurrentRegion.Rows.Count)
.Formula = "=A1&B1&C1"
.Value = .Value
.AdvancedFilter xlFilterCopy, , Sheets("Sheet2").Range("A1"), True
End With




With Sheets("Sheet2")
For Each ConcatCode In .Range("A2", .Range("A1").End(xlDown))
Sheets("Sheet1").Range("A1").AutoFilter 5, ConcatCode
ConcatCode(, 2) = WorksheetFunction.Subtotal(9, Sheets("Sheet1").Range("A1").CurrentRegion.Columns(4))
Next ConcatCode
End With




Sheets("Sheet1").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

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

This an Excel and Excel VBA forum. If you can't get the output into Excel then there may be better places for you to direct your question

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 file..you'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..

Sub test()
    Dim r As Range, i As Long
    With CreateObject("VBScript.RegExp")
        .Pattern = "\w+(?=(</|.pdf|.jpg))"
        .Global = False
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                    r(, 2).Value = .Execute(r.Value)(0)
            End If
        Next
    End With
End Sub

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

Sub test()
    Dim r As Range, i As Long
    With CreateObject("VBScript.RegExp")
        .Pattern = "\w+(?=.pdf)"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                    r(, 2).Value = .Execute(r.Value)(0)
            End If
        Next
    End With
End Sub

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.

Sub FilterIt()
Dim i As Integer, t As Integer
Dim FiltActvity As String
FiltActivity = "A"
With Sheets("Sheet1").Range("A1").CurrentRegion


'//Filter the range by the desired activity
.AutoFilter 1, FiltActivity


'//cycle through the columns where there is data in rows we need to process, ie from column no 2 to the last column 
For i = 2 To .Columns.Count


'//If after filtering for that activity, the sum of all numbers still showing in column i is >0, then we need the 
'//name of the person from that column heading
If WorksheetFunction.Subtotal(9, .Columns(i)) > 0 Then


'//Given that we're now interested in that person, we need to output the name in Sheet2 sequentially 
'//down the rows
t = t + 1


'//Going down the rows in Sheet2, the cell entry corresponds with us going across the column headers in Sheet1
Sheets("Sheet2").Cells(t, 2) = Sheets("Sheet1").Cells(1, i)
End If
Next i


'//We've entered the required names in colB starting from B1. To enter the activity in colA, we just need the
'//same range address in col B, but shifted one column to the left. Once we have that range, we can enter 
'//the Activity name into it in one go.
With Sheets("Sheet2")
.Range("B1", .Range("B" & Rows.Count).End(xlUp)).Offset(, -1).Value = FiltActivity
End With
End With


'//Remove autofilter
Sheets("Sheet1").AutoFilterMode = False
End Sub

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 to..xyz"
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: Dynamic Dropdown

Try the attached. All ranges dynamic and both dropdowns dynamic. Just paste your data into columns A and B and view the dropdowns in G11 and H11

Re: Find missing number from 0-9

Array formula entered in B4:B13

=IF(FREQUENCY(A4:A12,{0,1,2,3,4,5,6,7,8,9})=0,ROW(1:10)-1,"")

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
=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)
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

=TEXT(EOMONTH(TODAY(),-1),"mmm")

which would give you Feb if calculated today

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

Re: How to replace a string character.

If the string to replace is always the same number of characters long, you could use

=TRIM(SUBSTITUTE(A1,MID(A1,FIND("(#",A1),15),""))