Posts by Biz

Re: Generate list from excel fields

Quote from jindon;574831

Use one of these

Code

Sub InCell()
Dim a, i As Long, n As Long
a = Range("a1").CurrentRegion.Value
n = 1
With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(a, 1)
        If Not .exists(a(i, 1)) Then
            n = n + 1
            a(n, 1) = a(i, 1)
            a(n, 2) = a(i, 2)
            .Item(a(i, 1)) = n
        Else
            a(.Item(a(i, 1)), 2) = _
            a(.Item(a(i, 1)), 2) & ", " & a(i, 2)
        End If
    Next
End With
Range("d1").Resize(n, 2).Value = a
End Sub




Sub InColumn()
Dim a, i As Long, n As Long, maxCol As Long, w
a = Range("a1").CurrentRegion.Value
ReDim Preserve a(1 To UBound(a, 1), 1 To 100)
With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(a, 1)
        If Not .exists(a(i, 1)) Then
            n = n + 1
            a(n, 1) = a(i, 1)
            a(n, 2) = a(i, 2)
            .Item(a(i, 1)) = VBA.Array(n, 2)
        Else
            w = .Item(a(i, 1))
            w(1) = w(1) + 1
            If w(1) > UBound(a, 2) Then
                ReDim Preserve a(1 To UBound(a, 1), 1 To w(1))
            End If
            a(w(0), w(1)) = a(i, 2)
            maxCol = Application.Max(maxCol, w(1))
            .Item(a(i, 1)) = w
        End If
    Next
End With
Sheets(2).Range("a1").Resize(n, maxCol).Value = a

Display More

Nice script

Biz

Dear All,

I was creating a model and I though maybe useful for Superannuation Planning.
Imagine being 25ys old and having this great tool to plan it so flexible.
Hope life was this flexible.

Any suggestions please let me know.

Biz

Re: Lookup and Concatenate Strings

cross posting at Mr Excel

http://www.mrexcel.com/forum/showthread.php?t=561916

Dear All,

Don't you hate when users save xlsm as xlsx by accident and macros disappear in Excel 2007 & 2010
Why not force xlsm save only?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
     
    Dim varWorkbookName As String 
    Dim FileFormatValue As Integer 
     
    On Error Goto Quit 
    Application.EnableEvents = False 
     
    If SaveAsUI = True Then 
        varWorkbookName = Application.GetSaveAsFilename( _ 
        fileFilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm") 
        Cancel = True 
         
        If varWorkbookName <> "False" Then 
            Select Case LCase(Right(varWorkbookName, Len(varWorkbookName) - InStrRev(varWorkbookName, ".", , 1))) 
            Case "xlsm": FileFormatValue = 52 
            End Select 
             
            ActiveWorkbook.SaveAs varWorkbookName 
        End If 
    End If 
     
Quit: 
     
    If Err.Number > 0 Then 
        If Err.Number <> 1004 Then 
             
            MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _ 
            "Title", vbCritical 
             
        End If 
    End If 
     
    Application.EnableEvents = True 
     
End Sub

Re: Tax Calculations In Excel

Would it be nice if we goal seek salaries this easily?

Biz

Dear All,

Attached worksheet can calculate Gross Salary needed to achieve your desired net income after tax. It uses Goal Seek VBA by clicking "Calculate" button.

If you need just calculate net salary then type in cell B19. Don't click "calculate" button.

The tax rates can be modified as they not password protected to match your regional settings.

Biz

Re: Named Formula To Return The Nth Largest Value From Range

Is it posiible to post a small sample workbook? The other problem you could have if multiple values that are same. You may need formula that find a position of 1st value instead.

Figured out VBA code that works.

Dim SortOrder As Variant
Dim Ndx As Long


Application.ScreenUpdating = False


Worksheets("Sort Order").Activate
With Range(Range("A1"), Range("A1").End(xlDown).Offset(-1, 0))
    For Ndx = .Cells.Count To 1 Step -1
        Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
    Next Ndx
End With


Application.ScreenUpdating = True


Sheets("Overview").Select
Range("A2").Select

Biz

Re: Unprotecting A Protected Worksheet

Try

Sub MyMacro()






Sheet1.Unprotect Password:="Secret"


	'YOUR CODE


	Sheet1.Protect Password:="Secret"


End Sub

Re: Formula To Locate Dates Within Financial Year And Provide Cumulative Total

=SUMPRODUCT((array1=condition1)*(array2=condition2),(array3))
which works as the '*' operator is only required to coerce the conditional arrays that resolve to TRUE/FALSE into numeric values
(1/0). Thus, it is also possible to coerce each of the conditional arrays individually by multiplying them by 1

Hope quick explaination above helps.

Source http://www.xldynamic.com/source/xld.SUMPRODUCT.html#format

Biz

Re: Formula To Locate Dates Within Financial Year And Provide Cumulative Total

Pls find attached your solution.

Biz

Re: Create a Custom Sort Order

Hi Dave,

Dynamic range is needed because if need new worksheets tabs are added then someone would have copy and paste new worksheets in Sort Order worksheet tab. The only reason I am trying to use dynamic range is to avoid someone manually changing the range in the code below

With Worksheets("Sort Order").Range("A1:A79")

.

replaced with

With Worksheets("Sort Order").Range("sheetsorder")

With Range code with dyanmic range name fails. Not sure why it fails.
Hopeful someone can help.

Biz

Re: Create a Custom Sort Order

Quote from Dave Hawley

Enter "Custom Sort" into the Excel help.

No idea what your Worksheet tabs and "coincidence" have to with creating a custom sort order.

Dave you are right! My only question is then if the code below works

With Worksheets("Sort Order").Range("A1:A79")

then why does the code fail with

With Worksheets("Sort Order").Range("sheetsorder")

I have created a dynamic range called sheetsorder. The only reason I am creating dynamic range if more worksheets are added to summary then I need to sort the worksheets accordingly.

Biz

Re: Loop with dynamic range name

Quote from Dave Hawley

You are re-inventing the wheel and a slow Wagon wheel at that. Excel's built in sort allows you to define a Custom Sort.

Please stop assumming your means to an end in Thread Titles, it buries answers under misleading Titles.

Code

it does not work

That is of no help to anyone trying to help you. Do you see the Doctor and say "I don't feel well" and nothing more?

Hi Dave,

You are correct to certain extent. I have lots worksheet tabs and summary tab. The way worksheet tabs are listed do not necessarily coincidence with Excel sorts that are available according to Summary tab. I have created a worksheet with worksheet tab names as per summary tab.

Eg Petty Cash accounts starts with 38031 where as Revenue received in advance starts from 22500.

Kind Regards,

Biz

Dear All,

I am trying to create custom sort list. It works below when I define range as A1:A79.

[VBA]Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long
Application.ScreenUpdating = False
With Worksheets("Sort Order").Range("A1:A79")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With
Application.ScreenUpdating = True
End Sub
[/VBA]

I have created a dynamic range called sheetsorder. If I revise my code it does not work.

[VBA]Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long
Application.ScreenUpdating = False
With Worksheets("Sort Order").Range("sheetsorder")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With
Application.ScreenUpdating = True
End Sub
[/VBA]

I have attached my workbook for your review.

Kind Regards

Biz

Re: Lookup Until Conditional Match Then Return Last Value In Row

Quote from Krishnakumar

Hi Domenic,

Nice to see you here again

Hi Kris/Domenic

Nice answers.

Biz

Re: Lookup Until Conditional Match Then Return Last Value In Row

Hi

I have answer but need toi create more helper columns. Your final answer in Column J.

Let me know if this helps.

Biz

Re: HLookup - Last String - with Conditional IF

Could you pls post a sample workbook?

Biz

Re: Sum By Conditions & Rolling Monthly Total

You could also you sumproducts instead DSUM

=SUMPRODUCT(($A$6:$A$155=$C166)*(D$6:D$155))

Re: Convert Multiple Letters In Single Cells To Numbers

Hi Kris,

Thank you for your alternative approach.

Biz