Search:

Type: Posts; User: shg; Keyword(s):

Page 1 of 6 1

Search: Search took 0.62 seconds.

by shg
Replies
2
Views
45

Re: Set array formula

You can’t enter an array formula longer than 255 characters via VBA in a single go. At http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/, **** Kusleika shows...
2. Thread: Can I Code a macro that has Saved Text descriptions based on a criteria?

by shg
Replies
18
Views
236

Re: Can I Code a macro that has Saved Text descriptions based on a criteria?

If you were using formulas, it would be a simple VLOOKUP, no?

So put a lookup table on a (perhaps hidden) sheet and use that in your code.
3. Thread: I need with my friend

by shg
Replies
10
Views
189

Re: I need with my friend

Format E2 as General.

The formula in C2 should be =DATEDIF(B2, A2, "y")

by shg
Replies
9
Views
216

A
B
C
D
E
F
1
Group
Qty
CDF
5. Thread: Standard deviation of correlation matrix

by shg
Replies
6
Views
93

Re: Standard deviation of correlation matrix

Change the formula in those cells so they return blanks on and above the main diagonal. Blanks are ignored by STDEV.
6. Thread: Help converting weird date format

by shg
Replies
5
Views
120

Re: Help converting weird date format

Messy ...

A
B
C
115-May
01 May 2015B1: =--(1 & MID(A1, FIND("-", A1) + 1, 4) & LEFT(A1, FIND("-", A1) - 1))

216-Mar

by shg
Replies
3
Views
196

Re: Time Conversion

You're welcome.

by shg
Replies
3
Views
196

Re: Time Conversion

c
d
e
f
4
8.00
8.30
0.50e4: =dollarde(d4, 60) - dollarde(c4, 60)

5
9. Thread: Unprotect sheet error in VBA

by shg
Replies
19
Views
277

Worksheets

by shg
Replies
5
Views
147

Or ...

A
B
C
D
E
1
Site

by shg
Replies
5
Views
147

Re: % increase.

One way, perhaps:

A
B
C
D
E
1
Site
12. Thread: Solver: Constraint for values in a range

by shg
Replies
1
Views
57

Re: Solver: Constraint for values in a range

SolverOk SetCell:="B94", _
MaxMinVal:=2, _
ValueOf:=0, _
ByChange:="AJ55:AJ84", _
Engine:=1
...

by shg
Replies
7
Views
114

A
B
C
1
data
lbs

2
112.3

by shg
Replies
15
Views
246

Re: Autofill AAAA-AAAB etc

UDF?

A
B
1AAAAA1: =ColLtr(ROW() + 18278)

2AAAB

3AAAC

by shg
Replies
3
Views
72

Re: Noticed odd new functionality after upgrading to Excel 2016

You're welcome.

by shg
Replies
1
Views
72

Re: Using Application.OnTime

You can pass scalar arguments, it's just a little fussy:

Sub main()
Range("A1").Value = "Bob"
Application.OnTime EarliestTime:=Now(), Procedure:=CallString("mySub", "Test", "A1")
End Sub...

by shg
Replies
3
Views
72

18. Thread: Erasing Arrays after use question

by shg
Replies
3
Views
102

Re: Erasing Arrays after use question

You're welcome.

More compactly,

Function IsArrayInitialized(av As Variant) As Boolean
On Error Resume Next
IsArrayInitialized = Not IsError(UBound(av))
Err.Clear
End Function
19. Thread: decimals to feet and inches with excel 2002

by shg
Replies
6
Views
132

Re: decimals to feet and inches with excel 2002

Or =B1 & TEXT(C1,"' 0 #/##""")

Is there a question?
20. Thread: Erasing Arrays after use question

by shg
Replies
3
Views
102

Re: Erasing Arrays after use question

Function IsArrayInitialized(av As Variant) As Boolean
Dim i As Long

On Error Resume Next
If IsArray(av) Then
i = UBound(av)
If Err.Number Then Err.Clear Else...
21. Thread: Does 'On Error Resume Next' reset error?

by shg
Replies
1
Views
61

Re: Does 'On Error Resume Next' reset error?

It does, but I would use Sub B just because it makes clear what the code is doing.
22. Thread: Scientific Notation Removes values when converting cell to Number

by shg
Replies
3
Views
162

Re: Scientific Notation Removes values when converting cell to Number

You're welcome.
23. Thread: Scientific Notation Removes values when converting cell to Number

by shg
Replies
3
Views
162

Re: Scientific Notation Removes values when converting cell to Number

The data needs to be imported as Text and remain as text. Numbers in Excel (64-bit IEEE Doubles) only every display a max of 15 significant digits.
24. Thread: Randomly Distributing a Sum Over Cells

by shg
Replies
5
Views
232

Re: Randomly Distributing a Sum Over Cells

1. Copy the code below from the post
2. In Excel, press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window...
25. Thread: Randomly Distributing a Sum Over Cells

by shg
Replies
5
Views
232

Re: Randomly Distributing a Sum Over Cells

Like this?

A
B
C
D
E
F
G
Results 1 to 25 of 150
Page 1 of 6 1