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]

1st welcome to the boards
2nd please use code tags when posting code. start your code block with [ code ] (without the spaces) and end it with [ /code ] (without the spaces.

so it will look like this (takes up less window space)

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

Now on to your code.

Try not to use variants as often. Here you have four variables and they are all listed as variants. They are in fact ranges. So DIM them as ranges and add a 5th variable to replace the re-use of DLL. The DLL variable in your code changes from a range to a string. So DIM a string variable.

Notes*****

1. you can combine lines of DIMs by using a comma. Best Practices is to only combine DIMs of the same type.
2. Another best practices is to indent your code. Usually indent at blocks of activities like FORs, LOOPs, IFs, WITHs and SELECTs. This make the code easier to read. It also makes it easier to error check the code for forgotten block closings (END IFs, END WITHs, NEXTs etc.)
3. Nested IF statements are your friend. They save space and are easier to read/error check.

so something like:

Sub StandardDLL()

Dim Span As Range, CwHieght As Range, MIR As Range, DLL As Range
Dim strDLL As String
    Set DLL = Sheet2.Range("P23:T29")
    Set Span = Sheet2.Range("F23")
    Set CwHeight = Sheet2.Range("I23")
    Set MIR = Sheet2.Range("J23")
    
    If MIR = "No" Then
        If CwHEIGTH < 5000 Then
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("A6:G16").Value, 2, 0)
        Else
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("I6:O16").Value, 2, 0)
        End If
    ElseIf MIR = "Yes" Then
        If CwHEIGTH < 5000 Then
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("Q6:W16").Value, 2, 0)
        Else
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("Q23:W33").Value, 2, 0)
        End If
    End If
End Sub

In the above code you are using the worksheetfunction.vlookup to populate a variable, but then you are not doing anything with the variable. Even as you originally wrote the code, you have to do something with the DLL variable.


Now for the Questions to see if I can help you get the code to do what you want it to do.

I would first suggest recording a macro to do what you want. Just type the Vlookups into a cell to ensure that you are getting the results that you expect the code to return. Once you have recorded the macro you can step into it to see what excel writes out in VBA.

Excel is not very good at writing macros. The code will do what you recorded, but it is highly inefficient. But it is a good way to see how to accomplish a task in VBA.

IF you can not determine a solution from the recorded message, I will need a bit more info. We can start with the below questions:

Now in your code your IF statements did not address what should happen if MIR is = to 5000.

Can MIR be anything other than yes or no?

What do you expect to do with the strDLL (or your DLL) variable?

 

[mkkyB]

1st welcome to the boards
2nd please use code tags when posting code. start your code block with [ code ] (without the spaces) and end it with [ /code ] (without the spaces.

so it will look like this (takes up less window space)

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

Now on to your code.

Try not to use variants as often. Here you have four variables and they are all listed as variants. They are in fact ranges. So DIM them as ranges and add a 5th variable to replace the re-use of DLL. The DLL variable in your code changes from a range to a string. So DIM a string variable.

Notes*****

1. you can combine lines of DIMs by using a comma. Best Practices is to only combine DIMs of the same type.
2. Another best practices is to indent your code. Usually indent at blocks of activities like FORs, LOOPs, IFs, WITHs and SELECTs. This make the code easier to read. It also makes it easier to error check the code for forgotten block closings (END IFs, END WITHs, NEXTs etc.)
3. Nested IF statements are your friend. They save space and are easier to read/error check.

so something like:

Sub StandardDLL()

Dim Span As Range, CwHieght As Range, MIR As Range, DLL As Range
Dim strDLL As String
    Set DLL = Sheet2.Range("P23:T29")
    Set Span = Sheet2.Range("F23")
    Set CwHeight = Sheet2.Range("I23")
    Set MIR = Sheet2.Range("J23")
    
    If MIR = "No" Then
        If CwHEIGTH < 5000 Then
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("A6:G16").Value, 2, 0)
        Else
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("I6:O16").Value, 2, 0)
        End If
    ElseIf MIR = "Yes" Then
        If CwHEIGTH < 5000 Then
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("Q6:W16").Value, 2, 0)
        Else
            strDLL = WorksheetFunction.VLookup(Sheet2.Range("F23:F29").Value, _
                Sheet4.Range("Q23:W33").Value, 2, 0)
        End If
    End If
End Sub

In the above code you are using the worksheetfunction.vlookup to populate a variable, but then you are not doing anything with the variable. Even as you originally wrote the code, you have to do something with the DLL variable.


Now for the Questions to see if I can help you get the code to do what you want it to do.

I would first suggest recording a macro to do what you want. Just type the Vlookups into a cell to ensure that you are getting the results that you expect the code to return. Once you have recorded the macro you can step into it to see what excel writes out in VBA.

Excel is not very good at writing macros. The code will do what you recorded, but it is highly inefficient. But it is a good way to see how to accomplish a task in VBA.

IF you can not determine a solution from the recorded message, I will need a bit more info. We can start with the below questions:

Now in your code your IF statements did not address what should happen if MIR is = to 5000.

Can MIR be anything other than yes or no?

What do you expect to do with the strDLL (or your DLL) variable?

Hi RCBricker,

Thanks for getting back to me and thanks for the great tips. I've run a macro of what I want done basically.

Sub CwHLess5000()
'
' CwHLess5000 Macro
' To define values for D1 - D5
''
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C1:R16C7,3,TRUE)))"
    Range("P23").Select
    Selection.AutoFill Destination:=Range("P23:T23"), Type:=xlFillDefault
    Range("P23:T23").Select
    Range("Q23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C1:R16C7,4,TRUE)))"
    Range("R23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C1:R16C7,5,TRUE)))"
    Range("S23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C1:R16C7,6,TRUE)))"
    Range("T23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C1:R16C7,7,TRUE)))"
    Range("T24").Select
End Sub
Sub CwHeightGreaterThanOrEqual5000()
'
' CwHeightGreaterThanOrEqual5000 Macro
' define table selection
'
'
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C9:R16C15,3,TRUE)))"
    Range("I23").Select
    ActiveCell.FormulaR1C1 = "5000"
    Range("P23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C9:R16C15,3,TRUE)))"
    Range("P23").Select
    Selection.AutoFill Destination:=Range("P23:T23"), Type:=xlFillDefault
    Range("P23:T23").Select
    Range("P23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C9:R16C15,3,TRUE)))"
    Range("P23").Select
    Selection.AutoFill Destination:=Range("P23:T23"), Type:=xlFillDefault
    Range("P23:T23").Select
    Range("Q23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C9:R16C15,4,TRUE)))"
    Range("R23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C9:R16C15,5,TRUE)))"
    Range("S23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C9:R16C15,6,TRUE)))"
    Range("T23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C9:R16C15,7,TRUE)))"
    Range("T24").Select
End Sub
Sub CwHLess5000MIRY()
'
' CwHLess5000MIRY Macro
' For MIR yes and CwH less 5000
'
'
    Range("P23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R6C17:R16C23,3,TRUE)))"
    Range("P23").Select
    Selection.AutoFill Destination:=Range("P23:T23"), Type:=xlFillDefault
    Range("P23:T23").Select
    Range("Q23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R6C17:R16C23,4,TRUE)))"
    Range("R23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R6C17:R16C23,5,TRUE)))"
    Range("S23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R6C17:R16C23,6,TRUE)))"
    Range("T23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9<5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R6C17:R16C23,7,TRUE)))"
    Range("U23").Select
End Sub
Sub CwHGreaterEqual5000MIRY()
'
' CwHGreaterEqual5000MIRY Macro
' For MIR Yes and CwH greater or equal to 5000
'
'
    Range("I23").Select
    ActiveCell.FormulaR1C1 = "5000"
    Range("P23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R23C17:R33C23,3,TRUE)))"
    Range("P23").Select
    Selection.AutoFill Destination:=Range("P23:T23"), Type:=xlFillDefault
    Range("P23:T23").Select
    Range("Q23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R23C17:R33C23,4,TRUE)))"
    Range("R23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R23C17:R33C23,4,TRUE)))"
    Range("R23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R23C17:R33C23,5,TRUE)))"
    Range("S23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R23C17:R33C23,6,TRUE)))"
    Range("T23").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC6="""",RC9>=5000&RC10=""YES"",(VLOOKUP(RC6,CTI!R23C17:R33C23,7,TRUE)))"
    Range("U23").Select
End Sub

To answer your questions, MIR can only be "Yes" or "No". If CwHeight is = 5000, values returned will be from the same range as for CwHeight >5000

Hope this helps and hope the information provided can better help you understand what am after.

Cheers

 

[RCBricker]

Sorry I was out of the office.

So do you want the actual formulas to be written into the cells or just the results of the VLOOKUP?

The easiest (and best) way to do this is dynamically. So lets get some field headers into place.

It looks to me that we are applying the vlookup in rows P-T.

What are the names of those fields?

What row does your data start on?

our Vlookup is looking at columns 6,9, & 10.

What are the names of those fields?

once I have those answers and using your recorded macro, I can write something more dynamic up.

 

[mkkyB]

Hi,

No worries.
I want just the result of the VLOOKUP in the cells (P-T).
Cell names P-T are D1 - D5 respectively.

Column 6 is the span value in the sheet with matching span values in CTI
Columns 9 & 10 are as per the 4 conditions below.

'CTI' is the sheet that is being looked at to retrive the data.The rows within CTI for each condition below are:

SPAN

A

d1

d2

d3

d4

d5

<colgroup><col width="72" style="width: 54pt;" span="7"> <tbody> </tbody>

Conditions:

So for CwHeight < 5000 and M.I.R = "No" data starts from A6:G16
So for CwHeight >= 5000 and M.I.R = "No" data starts from I6:O16
So for CwHeight <5000 and M.I.R = "Yes" data starts from Q6:W16
So for CwHeight >= 5000 and M.I.R = "Yes" data starts from Q23:W33


Hope this provides a bit more information.

Thanks

 

[RCBricker]

Hi,

No worries.Column 6 is the span value in the sheet with matching span values in CTI
Columns 9 & 10 are as per the 4 conditions below.

I want just the result of the VLOOKUP in the cells (P-T).
Cell names P-T are D1 - D5 respectively.


'CTI' is the sheet that is being looked at to retrive the data.The rows within CTI for each condition below are:

SPAN

A

d1

d2

d3

d4

d5

<colgroup><col width="72" style="width: 54pt;" span="7"> <tbody> </tbody>

Conditions:

So for CwHeight < 5000 and M.I.R = "No" data starts from A6:G16
So for CwHeight >= 5000 and M.I.R = "No" data starts from I6:O16
So for CwHeight <5000 and M.I.R = "Yes" data starts from Q6:W16
So for CwHeight >= 5000 and M.I.R = "Yes" data starts from Q23:W33


Hope this provides a bit more information.

Thanks

ok last set of questions:

here is the formula you recorded

"=IF(RC6="""",RC9<5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C1:R16C7,3,TRUE)))"

I need you to walk me through this. It looks like you are saying that if F1 is nothing then if I9<5000 and J1="NO", vlookup(F1,CTI!R6,C1:R16:C7,3,true))

I am fine with the if F1 is not "" then bring in the vlookup. However, if F1 is "" what is supposed to happen?
what row does your data start on for the sheet that you are pulling the data INTO?

what are the field headers for CTI for the following columns:
A
G
I
O
Q
W

and finally for columns Q and W you have two different data ranges 6:16 and 23:33 is there a logical way to tell excel where the two ranges are located? Like field headers but obviously there shouldn't be another set of field headers are row 22 unless it is a different table.

 

[mkkyB]

ok last set of questions:

here is the formula you recorded

"=IF(RC6="""",RC9<5000&RC10=""NO"",(VLOOKUP(RC6,CTI!R6C1:R16C7,3,TRUE)))"

I need you to walk me through this. It looks like you are saying that if F1 is nothing then if I9<5000 and J1="NO", vlookup(F1,CTI!R6,C1:R16:C7,3,true))

I am fine with the if F1 is not "" then bring in the vlookup. However, if F1 is "" what is supposed to happen?
what row does your data start on for the sheet that you are pulling the data INTO?

what are the field headers for CTI for the following columns:
A
G
I
O
Q
W

and finally for columns Q and W you have two different data ranges 6:16 and 23:33 is there a logical way to tell excel where the two ranges are located? Like field headers but obviously there shouldn't be another set of field headers are row 22 unless it is a different table.

Error on my part, I had sent you this recorded macro before I noticed I had F1 as nothing. F1 is not nothing, F1 is whatever Span length has been manually input. Then VLOOKUP finds corresponding span length in CTI and returns the corresponding values for d1-5. The table used from CTI is then dependent on which of the 4 conditions are satisfied

The field headers for CTI columns are as I gave before.
So,
A - Span, G - d5; I - Span, O - d5; Q - Span, W - d5.
For Q and W with ranges 6:16 and 23:33, it is only that way because the table for MIR = "Yes" and CwHeight >= 5000 is right below range 6:16.
Yes, the two ranges are 2 different tables.

Hope it is now all clear.

Thanks

 

[RCBricker]

ok its your data, but you don't really need two separate tables to make this work. Same with the three areas. A-G,I-O and Q-W technically could all be stack one atop the other in A-G (actually would make this easier).

I say that only from the point of few of this task. You may have other reasons for separating the data in this fashion.

 

[RCBricker]

OK I am about out of here for the weekend. Try this code and we can work on it next week if needs be.

Sub StandardDLL()

Dim Span As Range, CwHieght As Range, MIR As Range, DLL As Range
Dim strDLL As String
Dim lngROW As Long, lngCOL As Long, lngROWcti As Long, lngCOLcti As Long
Dim rngSPAN As Range, rngCW As Range, rngMIR As Range, rngDLL As Range, _
    rngHEAD As Range, rngHEADa As Range, rngHEADi As Range, rngHEADo As Range
Dim ws As Worksheet, wsCTI As Worksheet
Dim wb As Workbook
Dim intD1 As Integer, intSPAN As Integer, intCW As Integer, _
    intMIR As Integer, intDLL5 As Integer, int1 As Integer, _
    int7 As Integer, int9 As Integer, int15 As Integer, _
    int17 As Integer, int23 As Integer, intD5 As Integer
Dim varI As Variant

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet4")
Set wsCTI = wb.Sheets("CTI")

With wsCTI
    lngROWcti6 = wsCTI.Range("Q6").End(xlDown).Row
    lngROWcti23 = wsCTI.Range("Q23").End(xlDown).Row
    lngCOLcti = wsCTI.Cells(1, wsCTI.Columns.Count).End(xlToLeft).Column
    Set rngHEADa = wsCTI.Range(wsCTI.Cells(1, 1), wsCTI.Cells(1, 8))
    Set rngHEADi = wsCTI.Range(wsCTI.Cells(1, 9), wsCTI.Cells(1, 16))
    Set rngHEADo = wsCTI.Range(wsCTI.Cells(1, 17), wsCTI.Cells(1, 23))
    int1 = rngHEADa.Find("Span").Column
    int7 = rngHEADa.Find("d5").Column
    int9 = rngHEADi.Find("Span").Column
    int15 = rngHEADi.Find("d5").Column
    int17 = rngHEADo.Find("Span").Column
    int23 = rngHEADo.Find("d5").Column
End With

With ws
    lngROW = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    lngCOL = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Set rngHEAD = ws.Range(ws.Cells(1, 1), ws.Cells(1, lngCOL))
    intD1 = rngHEAD.Find("D1").Column
    intD5 = rngHEAD.Find("D5").Column
    intSPAN = rngHEAD.Find("Span").Column
    intCW = rngHEAD.Find("CwHeight").Column
    intMIR = rngHEAD.Find("M.I.R.").Column
End With

    For varI = 1 To lngROW
        If ws.Cells(varI, intMIR).Value = "No" Then
            If ws.Cells(varI, intCW).Value < 5000 Then
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(6, int1), _
                        wsCTI.Cells(lngROWcti6, int7)), intNUM, True)
                    intNUM = intNUM + 1
                Next varj
            Else
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(6, int9), _
                        wsCTI.Cells(lngROWcti6, int15)), intNUM, True)
                Next varj
                intNUM = intNUM + 1
            End If
        ElseIf ws.Cells(varI, intMIR).Value = "Yes" Then
            If ws.Cells(varI, intCW).Value < 5000 Then
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(6, int17), _
                        wsCTI.Cells(lngROWcti6, int23)), intNUM, True)
                    intNUM = intNUM + 1
                Next varj
            Else
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(23, int17), _
                        wsCTI.Cells(lngROWcti23, int23)), intNUM, True)
                    intNUM = intNUM + 1
                Next varj
            End If
        End If
    Next varI
End Sub

 

[mkkyB]

OK I am about out of here for the weekend. Try this code and we can work on it next week if needs be.

Sub StandardDLL()

Dim Span As Range, CwHieght As Range, MIR As Range, DLL As Range
Dim strDLL As String
Dim lngROW As Long, lngCOL As Long, lngROWcti As Long, lngCOLcti As Long
Dim rngSPAN As Range, rngCW As Range, rngMIR As Range, rngDLL As Range, _
    rngHEAD As Range, rngHEADa As Range, rngHEADi As Range, rngHEADo As Range
Dim ws As Worksheet, wsCTI As Worksheet
Dim wb As Workbook
Dim intD1 As Integer, intSPAN As Integer, intCW As Integer, _
    intMIR As Integer, intDLL5 As Integer, int1 As Integer, _
    int7 As Integer, int9 As Integer, int15 As Integer, _
    int17 As Integer, int23 As Integer, intD5 As Integer
Dim varI As Variant

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet4")
Set wsCTI = wb.Sheets("CTI")

With wsCTI
    lngROWcti6 = wsCTI.Range("Q6").End(xlDown).Row
    lngROWcti23 = wsCTI.Range("Q23").End(xlDown).Row
    lngCOLcti = wsCTI.Cells(1, wsCTI.Columns.Count).End(xlToLeft).Column
    Set rngHEADa = wsCTI.Range(wsCTI.Cells(1, 1), wsCTI.Cells(1, 8))
    Set rngHEADi = wsCTI.Range(wsCTI.Cells(1, 9), wsCTI.Cells(1, 16))
    Set rngHEADo = wsCTI.Range(wsCTI.Cells(1, 17), wsCTI.Cells(1, 23))
    int1 = rngHEADa.Find("Span").Column
    int7 = rngHEADa.Find("d5").Column
    int9 = rngHEADi.Find("Span").Column
    int15 = rngHEADi.Find("d5").Column
    int17 = rngHEADo.Find("Span").Column
    int23 = rngHEADo.Find("d5").Column
End With

With ws
    lngROW = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    lngCOL = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Set rngHEAD = ws.Range(ws.Cells(1, 1), ws.Cells(1, lngCOL))
    intD1 = rngHEAD.Find("D1").Column
    intD5 = rngHEAD.Find("D5").Column
    intSPAN = rngHEAD.Find("Span").Column
    intCW = rngHEAD.Find("CwHeight").Column
    intMIR = rngHEAD.Find("M.I.R.").Column
End With

    For varI = 1 To lngROW
        If ws.Cells(varI, intMIR).Value = "No" Then
            If ws.Cells(varI, intCW).Value < 5000 Then
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(6, int1), _
                        wsCTI.Cells(lngROWcti6, int7)), intNUM, True)
                    intNUM = intNUM + 1
                Next varj
            Else
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(6, int9), _
                        wsCTI.Cells(lngROWcti6, int15)), intNUM, True)
                Next varj
                intNUM = intNUM + 1
            End If
        ElseIf ws.Cells(varI, intMIR).Value = "Yes" Then
            If ws.Cells(varI, intCW).Value < 5000 Then
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(6, int17), _
                        wsCTI.Cells(lngROWcti6, int23)), intNUM, True)
                    intNUM = intNUM + 1
                Next varj
            Else
                intNUM = 3
                For varj = intD1 To intD1 + 4
                    ws.Cells(varI, varj).Value = _
                        WorksheetFunction.VLookup(ws.Cells(varI, _
                        intSPAN).Value, wsCTI.Range(wsCTI.Cells(23, int17), _
                        wsCTI.Cells(lngROWcti23, int23)), intNUM, True)
                    intNUM = intNUM + 1
                Next varj
            End If
        End If
    Next varI
End Sub

Hi,

Thanks for being such great help.

There are other data under A-G and I didn't want to have to scroll too far down when looking at the data to check the values for comparison. But yea you are right, stacking the tables up would have been easier.

I will give the code a try later in the week.

Cheers!