Creating UDF in VB

[seenfresh]

I have a relatively unique need to create a UDF in VB.

I am going to attempt to explain my objective and if anyone has any insight it would be much appreciated.

I need a function that when I click a command button (or shortcut key) the code will start.... first will ask me to input a number value into a text box to be used as a key identifier for a search basically my lookup criteria. Once this is stored to ask me what row I would like to start at to insert in my current worksheet all data rows from another worksheet that match my search criteria. I have had troubles in the past working with input functions in vb.... would anyone have any ideas on how to start this UDF?

Any help would be amazing

Thanks in advance,

 

[venkat1926]

UDF is a function which is something like a formula to give a result.
what you want is do some stepsl I guess what you require is macro

some more informtion is required.
any how I have prepared an experimental macro. try to run the macro with the changes indicated in the comments and see wheher you get what you wnat. if ncessary modify
yoy can have a button any of the sheets and assing this macro to tht button.;

[Sub test()
Dim x
Dim rrow As Integer
Dim cfind As Range
x = InputBox("type the number you want")

With Worksheets("sheet1")
'change name of the sheet above line if necessary
On Error Resume Next

Set cfind = .Cells.Find(What:=x, LookAt:=xlWhole, LookIn:=xlValues, after:=Range("a1"))
If cfind Is Nothing Then GoTo line1
'MsgBox cfind.Address
cfind.EntireRow.Copy
'in the macro text box may not be necessary
With Worksheets("currentsheet")
'change the name of the sheet in the above lline
rrow = InputBox("type the no. of the row you require")
.Cells(rrow, "a").PasteSpecial
.Range("a1").Select
End With 'currentsheet"
End With 'sheet1
GoTo line2
line1:
Application.CutCopyMode = False
MsgBox "there is no such value"
line2:

MsgBox "macro over"
End Sub

 

[seenfresh]

Thanks for your help!

I managed to create a function that does the job, it is as follows:

Sub DMAUpdates()
'
' DMAUpdates Macro

Applicationscreenupdating = False

Dim Criteria1 As String
Dim MyRow As String
Dim iRow As Long
Dim Report1 As Object
Dim Report2 As Object

Set Report1 = Worksheets("Report1")
Set Report2 = Worksheets("Report2")

Criteria1 = InputBox("Please enter the Ad ID you wish to find", "Enter Ad Id Here!", "Enter Ad Id Here!")
If Not Criteria1 = "" Then
MsgBox "The Ad Id you wish to search for is " & Criteria1

Else
GoTo Finish:

End If


 
MyRow = InputBox("Please enter the row you wish to insert the results", "Enter Row Number Here!", "Enter Row Number Here!")
If Not MyRow = "" Then
MsgBox "The row you wish to start at is " & MyRow

Else

GoTo Finish:


End If


For iRow = 1 To Report2.UsedRange.Rows.Count

If Report2.Cells(iRow, "A").Value = Criteria1 Then

Found1 = 1


CampaignA = Report1.Cells(MyRow - 2, "A").Value
SiteA = Report2.Cells(iRow, "I").Value
PlacementA = Report2.Cells(iRow, "H").Value
CPMA = Report1.Cells(MyRow - 2, "D").Value
DeliveredA = Report2.Cells(iRow, "J").Value


Report1.Cells(MyRow, "A").EntireRow.Insert

Report1.Cells(MyRow, "A").Value = CampaignA
Report1.Cells(MyRow, "B").Value = SiteA
Report1.Cells(MyRow, "C").Value = PlacementA
Report1.Cells(MyRow, "D").Value = CPMA
Report1.Cells(MyRow, "J").Value = DeliveredA


End If


Next iRow

If Found1 <> 1 Then

MsgBox "Please make sure you entered the correct Ad Id, otherwise it is not found!"

End If


Finish:

Applicationscreenupdating = True

End Sub[code/]

This does exactly what I need, my only issue is timing... I'm not sure if it can more efficient ..quicker

Any suggestions?

Thanks for all your help!

 

[venkat1926]

I had only a quick look and not a detailed one

a FUNCTION starts with
function (name of the function) (arguments )
and ends with the line
End function;.

a trivial example of function is (this is trivial because sqrt is an embeeded function in excel)

Function rt(c As Double) As Double

rt = c ^ (1 / 2)
End Function

now go to any cell in the sheet and type
=rt(2) you will get the answer.

here is little less trivial function

Function SheetExists(ShName As String) As Boolean
On Error Resume Next
SheetExists = Len(ActiveWorkbook.Sheets(ShName).Name)
End Function

if you want to test a sheet with name "r1" exists in the workbook you can have an associated macro

Sub test()

MsgBox SheetExists("r1")
End Sub

what you have written seems to be a macro. If does waht you want that mut be ok . go ahead.. Functions are very useful. read about that.

by the by the html end tab for the end of the code statements is [/code]




now

 

[seenfresh]

Thanks...

I need to understand the concepts between macros and functions better...

I originally thought a function was an action that involved data input from a user/external input and internal output... my bad!

Thanks for your help!

Cheers