Sub vlookup_macro()
Dim selectlookup As Range
Dim lookupval As String
Set selectlookup = Application.InputBox(prompt:="select the cell containing a lookup value", Type:=8)
MsgBox (selectlookup.Address)
lookupval = (selectlookup.Value)
MsgBox (lookupval)
Dim selectrange As Range
Dim selectedarea As String
Set selectrange = Application.InputBox(prompt:="select the cells containing lookup range", Type:=8)
selectedarea = selectrange.Address
selectedarea = Replace(selectedarea, "$", "")
Dim selectedarea2 As String
MsgBox (selectedarea)
Dim firstnum As Integer
Dim secondnum As Integer
selectedarea2 = Replace(selectedarea, ":", "")
firstnum = CInt(Mid(selectedarea2, 2, 1))
secondnum = CInt(Mid(selectedarea2, Len(selectedarea2), 1))
MsgBox ("firstnum is: " & firstnum & "secondnum is: " & secondnum)
Dim first As String
first = Mid(selectedarea, 1, 2)
MsgBox ("selected area is " & selectedarea)
Dim last As Integer
last = CInt(Mid(selectedarea, Len(selectedarea), 1))
MsgBox ("last is " & last)
Dim firstcolnum As Integer
firstcolnum = CInt((Range(selectedarea & 1).Column))
MsgBox (firstcolnum)
Dim secondcolnum As Integer
secondcolnum = CInt(Range(Mid(selectedarea, Len(selectedarea) - 1, 1) & 1).Column)
MsgBox (secondcolnum)
Range(first).Select
MsgBox (ActiveCell.Address)
Dim coluser As Integer
coluser = CInt(InputBox("Enter the column number"))
MsgBox (coluser)
Dim i As Integer
MsgBox (firstcolnum)
MsgBox (coluser)
'For i = 1 To (coluser - 1)
'ActiveCell.Offset(0, 1).Select
'Next i
MsgBox (ActiveCell.Address)
Dim test1 As Integer
Dim test2 As String
Dim result As String
Dim j As Integer
For i = 1 To ((secondnum - firstnum) + 1)
MsgBox ("lookupval " & lookupval & " and activecell.Value " & ActiveCell.Value & " are being compaired ")
If IsNumeric(lookupval) Then
If IsNumeric(ActiveCell.Value) Then
If CInt(lookupval) = CInt(ActiveCell.Value) Then
MsgBox ("found")
For j = 1 To (coluser - 1)
ActiveCell.Offset(0, 1).Select
Next j
MsgBox ("The result is " & ActiveCell.Value)
result = (ActiveCell.Value)
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Else
MsgBox ("got an issue")
Exit Sub
End If
Else
If Trim(lookupval) = Trim(ActiveCell.Value) Then
MsgBox ("found")
For j = 1 To (coluser - 1)
ActiveCell.Offset(0, 1).Select
Next j
MsgBox ("The result is " & ActiveCell.Value)
result = (ActiveCell.Value)
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
End If
Next i
MsgBox (result)
Dim position As Range
Set position = Application.InputBox(prompt:="select the cell for the lookup value to appear", Type:=8)
position.Value = result
End Sub
Dim selectlookup As Range
Dim lookupval As String
Set selectlookup = Application.InputBox(prompt:="select the cell containing a lookup value", Type:=8)
MsgBox (selectlookup.Address)
lookupval = (selectlookup.Value)
MsgBox (lookupval)
Dim selectrange As Range
Dim selectedarea As String
Set selectrange = Application.InputBox(prompt:="select the cells containing lookup range", Type:=8)
selectedarea = selectrange.Address
selectedarea = Replace(selectedarea, "$", "")
Dim selectedarea2 As String
MsgBox (selectedarea)
Dim firstnum As Integer
Dim secondnum As Integer
selectedarea2 = Replace(selectedarea, ":", "")
firstnum = CInt(Mid(selectedarea2, 2, 1))
secondnum = CInt(Mid(selectedarea2, Len(selectedarea2), 1))
MsgBox ("firstnum is: " & firstnum & "secondnum is: " & secondnum)
Dim first As String
first = Mid(selectedarea, 1, 2)
MsgBox ("selected area is " & selectedarea)
Dim last As Integer
last = CInt(Mid(selectedarea, Len(selectedarea), 1))
MsgBox ("last is " & last)
Dim firstcolnum As Integer
firstcolnum = CInt((Range(selectedarea & 1).Column))
MsgBox (firstcolnum)
Dim secondcolnum As Integer
secondcolnum = CInt(Range(Mid(selectedarea, Len(selectedarea) - 1, 1) & 1).Column)
MsgBox (secondcolnum)
Range(first).Select
MsgBox (ActiveCell.Address)
Dim coluser As Integer
coluser = CInt(InputBox("Enter the column number"))
MsgBox (coluser)
Dim i As Integer
MsgBox (firstcolnum)
MsgBox (coluser)
'For i = 1 To (coluser - 1)
'ActiveCell.Offset(0, 1).Select
'Next i
MsgBox (ActiveCell.Address)
Dim test1 As Integer
Dim test2 As String
Dim result As String
Dim j As Integer
For i = 1 To ((secondnum - firstnum) + 1)
MsgBox ("lookupval " & lookupval & " and activecell.Value " & ActiveCell.Value & " are being compaired ")
If IsNumeric(lookupval) Then
If IsNumeric(ActiveCell.Value) Then
If CInt(lookupval) = CInt(ActiveCell.Value) Then
MsgBox ("found")
For j = 1 To (coluser - 1)
ActiveCell.Offset(0, 1).Select
Next j
MsgBox ("The result is " & ActiveCell.Value)
result = (ActiveCell.Value)
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Else
MsgBox ("got an issue")
Exit Sub
End If
Else
If Trim(lookupval) = Trim(ActiveCell.Value) Then
MsgBox ("found")
For j = 1 To (coluser - 1)
ActiveCell.Offset(0, 1).Select
Next j
MsgBox ("The result is " & ActiveCell.Value)
result = (ActiveCell.Value)
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
End If
Next i
MsgBox (result)
Dim position As Range
Set position = Application.InputBox(prompt:="select the cell for the lookup value to appear", Type:=8)
position.Value = result
End Sub
No comments:
Post a Comment