How to get this working? And can I do it better?

[Andresen]

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

 

[Andresen]

I have improved it a bit.
I close this Thread.

 

[Fluff]

In future please do not mark a post as the solution, when it does not contain one. Thanks

 

[Andresen]

In future please do not mark a post as the solution, when it does not contain one. Thanks

Please help me,
I tried to find a delete button due to no replay.

Shall I only let it be there?
//S

 

[Fluff]

If you want to post the solution then you can mark that, otherwise just leave the thread as is.