Excel User Defined Functions

Reply #8

lottaloot — Wed, 26 Jul 2006 16:58:31 GMT

Thanks for these codes. You never know when you may need something like this.

I keep all the codes talked about here in a specific folder in my computer it's there in case I need it

Reply #7

guesser — Wed, 26 Jul 2006 05:12:44 GMT

I use the first one,

What these macros do is when you are in your excel spreadsheet of Keno numbers (or any other numbers) you want to search on, you enable this macro, it will ask you for how long of a string of numbers you want to search on, if you are looking for 6 numbers, you reply '6', if you are looking for 10, you reply '10', and then the next thing it does is ask you for the numbers you want it to search for, and it will find them (if they exist) and hilight each row. You nee... [ More ]

Reply #6

guesser — Wed, 26 Jul 2006 05:07:32 GMT

And here is another variant of the same:

Private Sub CommandButton1_Click()

Dim howmany As Integer, counter As Integer, found As Integer, searchstring() As String, text As String, testcount As Integer

Dim rowcount As Integer, columncount As Integer

howmany = InputBox( how many do you want to search for )

'MsgBox howmany

ReDim searchstring(howmany) As String

For counter = 1 To howmany

text = Search Item Str(counter)

searchstring(counter) = InputBox(text)

'MsgBox searchstring... [ More ]

Reply #5

guesser — Wed, 26 Jul 2006 05:06:23 GMT

Here is one macro:

Public Sub FindMatches()

Dim x As Variant

Dim MyValues() As String

Application.ScreenUpdating = False

vSearchValue = Trim(InputBox( Please enter values to search for. Place a space between each value to search for! vbCrLf vbCrLf example: 1.99 2.50 3.75 ))

vMatch = CInt(InputBox( How many values must match to flag row? ))

x = Split(vSearchValue, )

ReDim myvalue(0 To UBound(x))

For i = 0 To UBound(x)

myvalue(i) = CSng... [ More ]

Reply #4

LottoChica23 — Mon, 24 Jul 2006 22:42:39 GMT

Thanks for the info...

Reply #3

Bryan — Mon, 24 Jul 2006 22:24:09 GMT

Thanks Steve,

Much appreciated!

Bryan

Reply #2

CARBOB — Mon, 24 Jul 2006 21:50:37 GMT

Very good, Steve, thank you. As always, willing to share. I wish, I could spend more time learning macros.

Reply #1

winsumloosesum — Mon, 24 Jul 2006 21:48:15 GMT

Here is code for Sum of Digits in a cell.

For example:

In cell A1 you have the following Pick 3 number: 123

Follow the same routine for creating a User Defined Function (Above Post)

Enter the following code:

Function SumDigits(Number)

Dim i As Integer

For i = 1 To Len(Number)

SumDigits = SumDigits + Val(Mid(Number, i, 1))

Next i

End Function

Click cell B1

Click Shift+F3

Click User Defined in the Function Category

On the r... [ More ]

Excel User Defined Functions

winsumloosesum — Mon, 24 Jul 2006 21:19:42 GMT

Someone asked about this recently.

Thought I would share it with all.

I found this Code searching the Web.

Thought it would be a good learning tool for those interested in VBA (Visual Basic for Applications) and using User Defined Functions (UDF)

How to Create Excel User Defined Functions

Open up a new workbook. Get into VBA (Press Alt+F11) Insert a new module (Insert > Module) Copy and Paste the following code in the right box under Option Explicit

Fu... [ More ]