Some Examples of Vba and Subroutine
Autor: Akshat Paliwal • March 19, 2016 • Term Paper • 1,011 Words (5 Pages) • 598 Views
Some Examples of VBA and Subroutines ------------------- harshal@iimidr.ac.in
Sub Randomnumbers()
x = InputBox("How many random numbers you want to generate?")
For i = 1 To x
Range("A" & i).Value = WorksheetFunction.RandBetween(1, 400)
Next i
y = WorksheetFunction.Max(Range("a1:a" & x))
MsgBox ("Max is " & y)
End Sub
Sub clearll()
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Sub
Function addvalue(XYZ As Range) As Integer
addvalue = 0
For i = 1 To XYZ.Rows.Count
For j = 1 To XYZ.Columns.Count
If XYZ.Cells(i, j) >= 0 Then
addvalue = addvalue + XYZ.Cells(i, j).Value
End If
Next j
Next i
End Function
Function NVLOOKUP(lookupp As Variant, lookupprange As Range, m As Integer, n As Integer)
Count = 0
For i = 1 To lookupprange.Rows.Count
If lookupprange.Cells(i, 1) = lookupp Then
Countt = Countt + 1
If Countt = n Then
NVLOOKUP = lookupprange.Cells(i, m)
End If
End If
Next i
End Function
Function CompareStrings(string1 As String, string2 As String) As Integer
correcto = 0
For i = 1 To Len(string1)
If Mid(string1, i, 1) = Mid(string2, i, 1) Then
correcto = correcto + 1
End If
Next i
CompareStrings = correcto
End Function
Function sumdiagonally(myrange As Range)
sumdiagonally = 0
For i = 1 To myrange.Rows.Count
sumdiagonally = sumdiagonally + myrange.Cells(i, i)
Next i
End Function
Function vlookupflex(lookupp As Variant, lookupprange As Range, m As Integer, n As Integer)
For i = 1 To lookupprange.Rows.Count
If lookupprange.Cells(i, m) = lookupp Then
vlookupflex = lookupprange.Cells(i, n)
End If
Next i
End Function
Function SEARCHLOC(searchrange As Range, searchname As Variant, rowcol As Integer) As Integer
SEARCHLOC = 0
For i = 1 To searchrange.Rows.Count
For j = 1 To searchrange.Columns.Count
If searchrange.Cells(i, j) = searchname Then
If rowcol = 1 Then
SEARCHLOC = i
Else
SEARCHLOC = j
End If
End If
Next j
Next i
End Function
Sub Msgbox_Capure_Reply()
Dim lReply As Long
'Run by placing cursor within Procedure & push F5
lReply = MsgBox("Do you wish to continue.", vbYesNoCancel + vbQuestion)
Select Case lReply
Case vbYes
...