Posts by shg

Re: Excel list into Microsoft word

If you copy a named range from Excel and paste/link into Word, it might look like this:

[TABLE="width: 253"]

639

642

263

157

641

160

5

288

416

903

701

255

205

82

473

727

536

501

494

550

314

451

160

718

30

[/TABLE]

If you then do Alt+F9 (Toggle Field Codes), the field looks like this:

{LINK Excel.Sheet12 "C"\\somepath\somefile.xlsx" "RawData!wi" \a\f\4 \h}

... where RawData is the name of the worksheet and wi is the named range.

Assuming the named range is dynamic (i.e., it adjusts when data is added or removed), then insertions and deletions in Excel will track when you update fields in Word.

[TABLE="width: 253"]

[/TABLE]

Re: Possible combination of numbers in a list that total to the target amount

Welcome to the board.

That could be tens, thousands, or millions of possibilities. Want to provide a little more detail, including purpose?

Re: rounding to nearest 9 or 5

I think some posts got clobbered in the server switch.

I checked in Excel 2003, which is the oldest version I have installed.

Re: rounding to nearest 9 or 5

CEILING has been around a looong time, Ali. At least to Excel 2003, and likely well before that.

Re: VBA Custom Function - Automation Error

Maybe a little clean-up:

Public Function StartingVd(VdStart As Double, Uv As Double, _
                           LRC As Double, pfStart As Double, _
                           Rc As Double, Xc As Double, NumRun As Double) As Double
  
  Const Sqr3        As Double = 1.73205080756888


  StartingVd = VdStart * Uv * 1000# / (LRC * Sqr3 * (Rc / NumRun * pfStart + (Xc / NumRun * Sqr(1 - pfStart ^ 2))))
End Function

Re: UDF

[TABLE="class: grid"]

Row\Col

Hai cho Ba

aaBchHio

B1: =Alpha(A1)

[/TABLE]

Function Alpha(sInp As String) As String
  Const sLtr        As String = "aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ"
  Dim sChr          As String
  Dim i             As Long


  For i = 1 To Len(sLtr)
    sChr = Mid(sLtr, i, 1)
    Alpha = Alpha & String(Len(sInp) - Len(Replace(sInp, sChr, "", 1, -1, vbBinaryCompare)), sChr)
  Next i
End Function

Re: Surface Solver - Solve 3d Surfaces

There's an updated add-in that addresses (I hope) regional settings that use a comma as the decimal separator at https://app.box.com/s/twlo4d44wm8d6aee1f39

The companion demo workbook is at https://app.box.com/s/730trr6cm1vr9ap25yac

Re: Tax Calculator

One way:

=SUMPRODUCT((A1 > {0,2,5,10}*100000) * (A1 - {0,2,5,10}*100000) * ({0,10,10,10}%))

Re: Method 'Range' of object '_Global' failed

The code will loop forever if it doesn't find 10 cells with a value of zero.

Re: Method 'Range' of object '_Global' failed

Object variables (which include ranges) require Set, but you don't need one here.

Sub setupAgents(curLoc As Variant)
    Dim i           As Long
    Dim iRow        As Long
    Dim iCol        As Long


    i = 1


    Do While i <= 10
        iRow = Int((20 - 2 + 1) * Rnd + 2)
        iCol = Int((21 - 2 + 1) * Rnd + 2)
        
        If Cells(iCol, iRow).Value2 = 0 Then
            curLoc(1, i) = iCol
            curLoc(2, i) = iRow
            i = i + 1
        End If
    Loop
End Sub

Also, the sequence of random numbers will repeat each time the VBE is reset. To avoid that, put Randomize at the beginning of the code.

Re: display certain text per value using custom format

If the cell can only have those three values,

[=1]"Not Complex";[=2]"Moderately Complex";"Very Complex"

Re: calculating the fraction of time

=A1/86400 and format as [m]:ss

Re: Function to interpolate a complex table

See http://www.excelforum.com/exce…plex-table.html?p=2821941

Re: Deleting checkboxes

An alternative is to use Marlett checboxes. They have the advantage of being on the same layer of the worksheet as other cells, and can be easily implemented in VBA.

Search the forum for many examples.

Re: Return Address of a cell that is smaller than x

=match(1000, a1:a50, -1) + 1

Re: Conflicting VBA codes

Every time your toggle button code changes the worksheet, the change event is firing, and your change event code isn't testing what got changed.

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me.Range("C26")
        If Target.Address = .Address Then Me.Rows("83:121").Hidden = .Value <> 1
    End With
End Sub

Re: Creating a List of all combinations (n choose k)

There's a workbook that may do what you want at http://www.box.net/shared/nqljsd3nlu

Re: Count the number of times a value is listed "consecutively"

=SUMPRODUCT(--(FREQUENCY(IF(A1:E1=1, COLUMN(A1:E1)), IF(A1:E1<>1, COLUMN(A1:E1)))>0))

The formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

Re: Simple solver constraint with VBA

SolverAdd CellRef:="F70", Relation:=3, FormulaText:="0"

Re: Force VBA to ignore Runtime Error 1004

Replace WorksheetFunction with Application; it returns a handle-able error. Make sure the VBE is configured to Break on unhandled errors.