I have an excel sheet where I twice a week are following up the reduction of the issue hours that is left. I am following up the progress.
I need to see the change of each row and I have built it up with normal v-lookup functions. But it becomes very complex and I have found very many faults and have problem to trust my output.
I cannot be sure that the headers, Defect, Feature, Other etc are always there. So I need to secure a check of that.
Sometimes I would like to have out the sum of a few columns in one row.
To make this more stable and easier to do correct and trouble shoot. I though of making a function that can handle this. (I have never done a function before just Sub.)
So I started to do a Sub and had the thought to transfer it to a Function when it works.
* In the code below I do not get the "VResult = VResult + Application.WorksheetFunction.VLookup(Target_Version, Data_Range, VCol(i), 0)" to work. How come?
* To transfer it to a Function I assume I can do as the following.
Function CalcdataVlookup(Data_Range As Range, Target_Version As String, Variable1 As String, Variable2 As String, Variable3 As String, Variable4 As String, variable5 As String) As String
and put the following in the end CalcdataVlookup = VResult
* Everything that makes the code more effective is appricieted
Sub test_Function()
Call CalcdataVlookup(Range("HH4:HN22"), "VCM5D 1.1", "Requirements", "", "", "", "")
End Sub
Sub CalcdataVlookup(Data_Range As Range, Target_Version As String, Variable1 As String, Variable2 As String, Variable3 As String, Variable4 As String, variable5 As String)
Dim Variable(1 To 5) As String
Dim VCol(1 To 5) As Long
Dim VTargetRow As Long
Dim i As Long
Dim ASheet As String
Dim VResult As Long
Dim VCell As Range
Variable(1) = Variable1
Variable(2) = Variable2
Variable(3) = Variable3
Variable(4) = Variable4
Variable(5) = variable5
ASheet = ActiveSheet.Name
VResult = 0
For i = 1 To 5
If Not Variable(i) = "" Then
Call FindInputColumn(VCol(i), Variable(i), ASheet, 4, Data_Range.Cells(1, 1))
VResult = VResult + Application.WorksheetFunction.VLookup(Target_Version, Data_Range, VCol(i), 0)
End If
Next i
' CalcdataVlookup = VResult
End Sub
Sub FindInputColumn(VInputCol As Long, VInputName As String, ASheet As String, VRow As Long, VAfter As Range)
Dim VFind As Range
Debug.Print "FindInputColumn " & ASheet
VInputCol = 0
Set VFind = Nothing
' Find column for Choice
With Worksheets(ASheet).Rows(VRow)
If VAfter = "" Then
Set VFind = .Find(VInputName, LookIn:=xlValues, LookAt:=xlWhole)
Else
Set VFind = .Find(VInputName, LookIn:=xlValues, LookAt:=xlWhole, after:=VAfter)
End If
If Not VFind Is Nothing Then
VInputCol = VFind.Column
End If
End With
Set VFind = Nothing
End Sub
I need to see the change of each row and I have built it up with normal v-lookup functions. But it becomes very complex and I have found very many faults and have problem to trust my output.
I cannot be sure that the headers, Defect, Feature, Other etc are always there. So I need to secure a check of that.
Sometimes I would like to have out the sum of a few columns in one row.
To make this more stable and easier to do correct and trouble shoot. I though of making a function that can handle this. (I have never done a function before just Sub.)
So I started to do a Sub and had the thought to transfer it to a Function when it works.
* In the code below I do not get the "VResult = VResult + Application.WorksheetFunction.VLookup(Target_Version, Data_Range, VCol(i), 0)" to work. How come?
* To transfer it to a Function I assume I can do as the following.
Function CalcdataVlookup(Data_Range As Range, Target_Version As String, Variable1 As String, Variable2 As String, Variable3 As String, Variable4 As String, variable5 As String) As String
and put the following in the end CalcdataVlookup = VResult
* Everything that makes the code more effective is appricieted
Sub test_Function()
Call CalcdataVlookup(Range("HH4:HN22"), "VCM5D 1.1", "Requirements", "", "", "", "")
End Sub
Sub CalcdataVlookup(Data_Range As Range, Target_Version As String, Variable1 As String, Variable2 As String, Variable3 As String, Variable4 As String, variable5 As String)
Dim Variable(1 To 5) As String
Dim VCol(1 To 5) As Long
Dim VTargetRow As Long
Dim i As Long
Dim ASheet As String
Dim VResult As Long
Dim VCell As Range
Variable(1) = Variable1
Variable(2) = Variable2
Variable(3) = Variable3
Variable(4) = Variable4
Variable(5) = variable5
ASheet = ActiveSheet.Name
VResult = 0
For i = 1 To 5
If Not Variable(i) = "" Then
Call FindInputColumn(VCol(i), Variable(i), ASheet, 4, Data_Range.Cells(1, 1))
VResult = VResult + Application.WorksheetFunction.VLookup(Target_Version, Data_Range, VCol(i), 0)
End If
Next i
' CalcdataVlookup = VResult
End Sub
Sub FindInputColumn(VInputCol As Long, VInputName As String, ASheet As String, VRow As Long, VAfter As Range)
Dim VFind As Range
Debug.Print "FindInputColumn " & ASheet
VInputCol = 0
Set VFind = Nothing
' Find column for Choice
With Worksheets(ASheet).Rows(VRow)
If VAfter = "" Then
Set VFind = .Find(VInputName, LookIn:=xlValues, LookAt:=xlWhole)
Else
Set VFind = .Find(VInputName, LookIn:=xlValues, LookAt:=xlWhole, after:=VAfter)
End If
If Not VFind Is Nothing Then
VInputCol = VFind.Column
End If
End With
Set VFind = Nothing
End Sub
