vba VLookup with many variants

[mkkyB]

Hi,

I am new to vba and would greatly appreciate any help I can get.

I am comfortable working with excel, however, I feel what am trying to achieve will be much suited for vba.
This is my code, ovbiously something is wrong. As I am new to vba I can't seem to figure out what is wrong.

I need DLL value result in sheet 2 (P23:T29) depending on the span (F23:F29), Cwheight (I23:I29)and MIR (J23:J29) in sheet 2.
In sheet 4, columns A6, I6, Q6 and Q23 all have the same range of span values (40 - 50). The rest of the range in each case is dependent of CwHeight and MIR values and the corresponding DLL value.
MIR is either "Yes" or "No"

Can someone help me with this please?

Thanks

Sub StandardDLL()
Dim Span As Variant
Dim DLL As Variant
Dim CwHieght As Variant
Dim MIR As Variant

Set DLL = Sheet2.Range("P23:T29")
Set Span = Sheet2.Range("F23")
Set CwHeight = Sheet2.Range("I23")
Set MIR = Sheet2.Range("J23")
If CwHeigth < 5000 & MIR = "No" Then
DLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, Sheet4.Range("A6:G16").Value, 2, 0)
End If
If CwHeigth > 5000 & MIR = "No" Then
DLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, Sheet4.Range("I6:O16").Value, 2, 0)
End If
If CwHeigth < 5000 & MIR = "Yes" Then
DLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, Sheet4.Range("Q6:W16").Value, 2, 0)
End If
If CwHeigth > 5000 & MIR = "Yes" Then
DLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, Sheet4.Range("Q23:W33").Value, 2, 0)
End If

End Sub

 

[RCBricker]

ok well once you test it we can move forward.