How do I work with range?

[Andresen]

Hi,
I will in the end try to use this Sub as a Function. (This will be like a multi Vlookup function.) But I cannot come through with the Range.

I try to run the test_Function() sub.
I get Compile error Type mismatch. And I cannot find why (see picture). This is the first time I use Range variables.

The data looks like this

Sub test_Function()
Call Calcdata("HH4:HN22", "VCM5D Reach 1.1", "Requirements", "", "", "", "")
End Sub



Sub Calcdata(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

Variable(1) = Variable1
Variable(2) = Variable2
Variable(3) = Variable3
Variable(4) = Variable4
Variable(5) = variable5

ASheet = ActiveSheet.Name

For i = 1 To 5
If Not Variable(i) Then Call FindInputColumn(VCol(i), Variable(i), ASheet, 4)
Next i

Call FindInputRow(VTargetRow, Target_Version, ASheet, 1)
' Find each Variable in range and sum the different Variables

VResult = 0
For i = 1 To 5
If VCol(i) > 0 Then
VResult = VResult + ActiveSheet.Cells(VTargetRow, VCol(i)).Value
End If
Next i
' Calcdata = VResult

End Sub

 

[Legacy 143009]

Your code is very hard to read and understand for me. From the first look, change this:

Call Calcdata(Range("HH4:HN22"), "VCM5D Reach 1.1", "Requirements", "", "", "", "")