Some Examples of Vba and Subroutine
Some Examples of VBA and Subroutines ——————- [email protected] Sub Randomnumbers()x = InputBox(“How many random numbers you want to generate?”)For i = 1 To xRange(“A” & i).Value = WorksheetFunction.RandBetween(1, 400)Next iy = WorksheetFunction.Max(Range(“a1:a” & x))MsgBox (“Max is ” & y)End SubSub clearll()ActiveCell.Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContentsEnd SubFunction addvalue(XYZ As Range) As Integeraddvalue = 0For i = 1 To XYZ.Rows.CountFor j = 1 To XYZ.Columns.CountIf XYZ.Cells(i, j) >= 0 Thenaddvalue = addvalue + XYZ.Cells(i, j).ValueEnd IfNext jNext iEnd FunctionFunction NVLOOKUP(lookupp As Variant, lookupprange As Range, m As Integer, n As Integer)Count = 0For i = 1 To lookupprange.Rows.CountIf lookupprange.Cells(i, 1) = lookupp ThenCountt = Countt + 1If Countt = n ThenNVLOOKUP = lookupprange.Cells(i, m)End IfEnd IfNext iEnd FunctionFunction CompareStrings(string1 As String, string2 As String) As Integercorrecto = 0For i = 1 To Len(string1)
If Mid(string1, i, 1) = Mid(string2, i, 1) Thencorrecto = correcto + 1End IfNext iCompareStrings = correctoEnd FunctionFunction sumdiagonally(myrange As Range)sumdiagonally = 0For i = 1 To myrange.Rows.Countsumdiagonally = sumdiagonally + myrange.Cells(i, i)Next iEnd FunctionFunction vlookupflex(lookupp As Variant, lookupprange As Range, m As Integer, n As Integer)For i = 1 To lookupprange.Rows.CountIf lookupprange.Cells(i, m) = lookupp Thenvlookupflex = lookupprange.Cells(i, n)End IfNext iEnd FunctionFunction SEARCHLOC(searchrange As Range, searchname As Variant, rowcol As Integer) As IntegerSEARCHLOC = 0For i = 1 To searchrange.Rows.CountFor j = 1 To searchrange.Columns.CountIf searchrange.Cells(i, j) = searchname ThenIf rowcol = 1 ThenSEARCHLOC = iElseSEARCHLOC = jEnd IfEnd IfNext jNext iEnd FunctionSub Msgbox_Capure_Reply()Dim lReply As LongRun by placing cursor within Procedure & push F5 lReply = MsgBox(“Do you wish to continue.”, vbYesNoCancel + vbQuestion) Select Case lReply Case vbYes