Posts by shades

Re: Formatting Line Graph

Howdy. I would set up two separate columns: Actual and Projected. Fill in appropriate cells under, and the have two lines as line charts. See if the attached helps.

HTH

Re: Writtennumber()

Here is J-Walk's code:

Function SPELLDOLLARS(cell) As Variant


    Dim Dollars As String
    Dim Cents As String
    Dim TextLen As Integer
    Dim Temp As String
    Dim Pos As Integer
    Dim iHundreds As Integer
    Dim iTens As Integer
    Dim iOnes As Integer
    Dim Units(2 To 5) As String
    Dim bHit As Boolean
    Dim Ones As Variant
    Dim Teens As Variant
    Dim Tens As Variant
    Dim NegFlag As Boolean


'   Is it a non-number?
    If Not IsNumeric(cell) Then
        SPELLDOLLARS = CVErr(xlErrValue)
        Exit Function
    End If


'   Is it negative?
    If cell < 0 Then
        NegFlag = True
        cell = Abs(cell)
    End If
    
    Dollars = Format(cell, "###0.00")
    TextLen = Len(Dollars) - 3


'   Is it too large?
    If TextLen > 15 Then
        SPELLDOLLARS = CVErr(xlErrNum)
        Exit Function
    End If


'   Do the cents part
    Cents = Right(Dollars, 2) & "/100 Dollars"
    If cell < 1 Then
        SPELLDOLLARS = Cents
        Exit Function
    End If


    Dollars = Left(Dollars, TextLen)


    Ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
    Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")


    Units(2) = "Thousand"
    Units(3) = "Million"
    Units(4) = "Billion"
    Units(5) = "Trillion"


    Temp = ""


    For Pos = 15 To 3 Step -3
        If TextLen >= Pos - 2 Then
            bHit = False
            If TextLen >= Pos Then
                iHundreds = Asc(Mid$(Dollars, TextLen - Pos + 1, 1)) - 48
                If iHundreds > 0 Then
                    Temp = Temp & " " & Ones(iHundreds) & " Hundred"
                    bHit = True
                End If
        End If
        iTens = 0
        iOnes = 0


        If TextLen >= Pos - 1 Then
            iTens = Asc(Mid$(Dollars, TextLen - Pos + 2, 1)) - 48
        End If


        If TextLen >= Pos - 2 Then
            iOnes = Asc(Mid$(Dollars, TextLen - Pos + 3, 1)) - 48
        End If


        If iTens = 1 Then
            Temp = Temp & " " & Teens(iOnes)
            bHit = True
        Else
            If iTens >= 2 Then
                Temp = Temp & " " & Tens(iTens)
                bHit = True
            End If
            If iOnes > 0 Then
                If iTens >= 2 Then
                    Temp = Temp & "-"
                Else
                    Temp = Temp & " "
                End If
                Temp = Temp & Ones(iOnes)
                bHit = True
            End If
        End If
        If bHit And Pos > 3 Then
            Temp = Temp & " " & Units(Pos \ 3)
        End If
    End If
    Next Pos


  SPELLDOLLARS = Trim(Temp) & " and " & Cents
  If NegFlag Then SPELLDOLLARS = "(" & SPELLDOLLARS & ")"
End Function

Re: Function "replace" Missing For Mac Vb

Quote from ByTheCringe2

Moving this to Technical Issues forum.

Should it be moved there?

This is strictly XL and VBA related.

Does this mean that someone who has to change code to account for XL 97 requires that the thread be moved to Technical Issues?

Re: Function "replace" Missing For Mac Vb

Howdy. In order to make the transition to using VBA for Mac, you have to make the changes in your code so that it is usable for Excel 97 on the Windows side (both Mac Excel X/2004 and XL 97 were based on VB 5).

Re: Writtennumber()

The Walkenbach code gives you what you want.

Re: Writtennumber()

Quote from zapacoman

I found the following and pasted it into the VBA page for that particular sheet...but can't get it to work...

Code

Function SPELLDOLLARS(cell) As Variant 
...
End Function

That is the one from Walkenbach's book/CD. How are you referencing this in the spreadsheet?

Re: Writtennumber()

If you have John Walkenbach's book (MS Excel 200* Formulas), check chapter 23, "SPELLDOLLARS" VBA function.

Re: VBA Conditional Format Based On Multiple Conditions

Howdy. yes it will take VBA.

Here is one approach. If you set up the color that you want for each condition on the CFControl worksheet (I have cities, you would need your conditions, and then the appropriate color index).

The code is automatic on the Data worksheet and will use the CFControl worksheet to change color as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
    Dim rng As Range
    ' Target is a range::therefore,it can be more than one cell
    ' For example,,someone could delete the contents of a range,
    ' or someone could enter an array..
    Set rng = Intersect(Target, Range("B2:G30"))
    If rng Is Nothing Then
        Exit Sub
    Else
        Dim cl As Range
        For Each cl In rng
            On Error Resume Next
            ' — The preceding line doesn ’t change the cell ’s background
            ' — color if the cell ’s value is not found in the range
            ' — that we specified ((rngcolors).
            cl.Font.ColorIndex = _
            Application.WorksheetFunction.VLookup(cl.Value, _
                ThisWorkbook.Sheets("CFControl").Range("rngColors"), 2, False)
            If Err.Number <> 0 Then
                cl.Font.ColorIndex = xlNone
            End If
        Next cl
    End If
End Sub

If you are using formulas and not typing in the values, then you would need something to trigger the response to change.

(Note, I put the values from CFCOntrol on the Data worksheet, just to give you an idea of what to enter int he cells.

Re: Lookup 2 Columns And Match To 2 Other Columns

Howdy. Add a column for concatenation (E). and beginning in E3, put this formula:

=F3&G3

Then copy down.

Then in cell C3, put this formula:

=INDEX($H$3:$H$17,MATCH($A3&$B3,$E$3:$E$17,FALSE),1)

And copy down as needed.

Change ranges appropriately.

Re: Open File Not In Internet Explorer

Howdy.

Perhaps this link will help:

MS Guide to Opening in Application

Re: Change Date Formulas To Values

Just a note: You set calculation to manual at the beginning of the code. Are you wanting to set it to Automatic at the end? If so, you didn't, it continues as Manual.

Re: Linking Names To Closed Workbooks

I think Andy is right on this. I wonder if it is the volatile functions that don't work (I remember OFFSET and INDIRECT both causing problems in this regard).

Re: Data Analysis

Quote from shades

I chose red font, but in your case change that red font to red font. That way if there are more they will be visible, and fewer, they will appear invisible.

I wrote this incorrectly. In the Conditional Formatting dialog, change the red font to white font, so that they will "disappear" against the white background of the cell.

Sorry for the confusion.

Re: File Size - Reduce?

Quote from royUK

What I really wondered was if you have your data in a classic Excel Table Format?

That would be my first question.

My second would be: What kind of formulas? That can make a difference.

My third question: What is the sequence of formulas and formulas relative to worksheets. You always want formulas pulling from behind, never back and forth.

Probably one of the biggest issues with formulas is the sequence of calculation. Generally, you should put raw data tabs/worksheets with the first in the sequence. Thus, I label all sheets this way:

data sheets:

A0_Data1
A1_Data2
A2_Data3
etc.

Then formula worksheets:

B0_Formulas1
B1_Formulas2
B2_Formulas3

etc.

The key is that you want formulas always looking backward never forward (thus, B1_Formula can look at any data sheet and B0_Formulas, but should not get data from B2_Formulas3). I have made significant changes and reduced file size and speed of calculations using this. See this for more information:

See Charles Williams site for more on this topic.

Re: Data Analysis

Okay, this might be more complicated, but it will do what you want.

There is a formula in cell A4

=D4-C4

Then I put numbers in column A begininng with cell A6 with 0, and then adding one to each cell down.

Then in cell C6 I put this formula:

=C$4+$A6

and copied down to row 20.

In cell E6, I put this formula:

=E$4

and copied down to row 20.

Then on the Work worksheet, I added a column A, and dynamic named range for it.

=OFFSET(Dates,0,-1)

In cell F6 I put this formula:

=INDEX(Data,MATCH(C6&E6,DataWS_LU,FALSE),6)

and copied down to row 20.

On worksheet Sheet3 I would hide column A, then I added conditional formatting for cells C9:F20. Select those cells, then in the dialog box, on far left, choose dropdown "Formula Is", then put this in the box to the right:

=$A9>$A$4

I chose red font, but in your case change that red font to red font. That way if there are more they will be visible, and fewer, they will appear invisible.

If you want to protext the sheet, then the users won't be able change any of this.

(I deleted some rows on the Work tab because of the file size)

HTH

Re: Formula To Return One Cell If Another Cell Is Blank

Howdy, and welcome to the grieving section.

You want the values separately in Columns A:C, E? Can you provide a sample worksheet with sample data?

Re: Data Analysis

Howdy.

What about some formulas and dynamic named ranges instead of VBA?

I changed your sheet name to Work, just for simplicity. Then Control has a some named ranged for using in drodowns. Also, dynamic named ranges on Work (based on Dates).

Then on Sheet3, I placed the Start Date with a dropdown, End Date also dropdown, and Weather Station. Then in Cell F5 I put this formula:

=SUMPRODUCT((Dates>=$C$4)*(Dates<=$D$4)*(DataWS=$E$4),DataHDD)

Add other combinations as needed to make it a dashboard. I would probably setup a couple of intermediate worksheets to feed a dashboard, but this gives you an idead.

You could make adjustments as necessary, and even protect the worksheet so they can only change dropdown cells.

This might be easier to maintain than VBA (depending on your skill level for each).

Just a thought.

Re: Off Topic Friday

Wait, I thought singular was ya'll, and plural was all ya'll. Or maybe that was only in Georgia.

Re: Daves Not Here?

I was in Hing Kong 31 years ago. Great place. I would love to go back!

Re: Vba, R.i.p?

Quote from Derk

My understanding is that for the Macintosh version Office 2008, VBA will not be supported. Moreover, I believe there will be no replacement for it beyond the more general Applescript. Microsoft's announced reasoning is it is too hard to make the conversion of the clunky Mac version of VBA to the more modern coding basis Office will be using.

Yeah, I may have seen the last update for myself (Office 2004). Since it runs VBA (as long as it is compatible with Office 97, it works on Mac). But with the 2008, it will not even run. At work we upgraded to 2003 just in the last 18 months (65,000 employees). So if they support VBA for 10 years, I'll be long gone into retirement. So may not bother with VB.net.

I know some companies that have depended on VBA for Macs (not heavy duty, but essential tasks). There is no way they can upgrade.

I wonder if a third-party language might offer the best cross-platform potential (Python, Ruby, etc.).