VBA: VLOOKUP with LOOP

[BrunoVeiga]

Good morning,


This is my first time here, I hope you guys are able to help me out. I recently started to work with VBA, I am finding it amazing, but really complex for a non-programming mindset person.
I have been mostly recording my VBAs and adjusting and improving as it goes, and it is a great way to learn. But I came across this topic that I cannot really find a way to implement. I have been watching videos and reading forums, but nothing exactly what I need. I am hoping someone is able to assist me here.


I would like to build a macro to automatize the below:


Combine information of 3 worksheets in one worksheet (Cognos).
I want to see in "Cognos" sheet only rows with NW status (NW status is shown at at Sheet VT11 in Colum J )


In the same macro I am also interested to run other details:
1. At Cognos sheet, delete rows of files that are not in the VT11 sheet and is not NW status in the Column J. until here it was fine, but... not further.
>The Cognos sheet should show only rows that are NW from the Column J at VT11 sheet.


2. At Cognos Sheet, Column C, =VLOOKUP(B2,'VT11'!A:B,2,FALSE) Loop until last Cognos sheet B2 row.
> Bring the Order # from VT11 sheet to Cognos sheet.


3. At Cognos Sheet, Column AK, implement =VLOOKUP(A2,TietanMasterExtra!A:BD,56,FALSE) Loop until last Cognos sheet A2 row.
> Bring the Additional Text info from TietanMasterExtra(BD) sheet to Cognos Sheet (AK)


4. At Cognos Sheet, Column AL, Loop until last row the =VLOOKUP(B2,'VT11'!A:O,14,FALSE)
> Bring the Name info from VT11sheet (N) sheet to Cognos Sheet (AL)


Thanks in advance for your help
Cheers
Bruno

 

[BrunoVeiga]

Last question (I hope).

where does the Reference number data for the Cognos sheet come from? I will paste manually a report in Cognos sheet, in this report, the reference number (Column A) will be there.

and did you see the question from post #8? Added here with my answer now:

Originally Posted by BrunoVeiga

I will be coping to cognos sheet the columns A,B, N.

2. At Cognos Sheet, Column C, =VLOOKUP(B2,'VT11'!A:B,2,FALSE).
3. At Cognos Sheet, Column AK, implement =VLOOKUP(A2,TietanMasterExtra!A:BD,56,FALSE) .
4. At Cognos Sheet, Column AL, Loop until last row the =VLOOKUP(B2,'VT11'!A:O,14,FALSE)



in the above description:

Column B is brought in from the VT11 sheet with the formula in Column C of Cognos - Correct
Column N is brought in from the VT11 sheet with the formula in Column AL of Cognos - Correct

How is column A from VT11 moved to cognos? Just a copy paste into column B of Cognos?

How is column A being brought from VT11 to Cognos? Cognos will already have Column A filled in with values from a report I will paste manually. Therefore, I will pass an entire report to Cognos sheet, where some columns are going to be blank, and I want to bring from other sheets matching data to fill in these empty columns. (C, AK and AL).

Thanks for your persistence and patience.

Cheers
Bruno

 

[RCBricker]

not column A of Cognos. How is Column A from VT11 brought over to Column B of Cognos?

 

[BrunoVeiga]

Thanks for asking

not column A of Cognos. How is Column A from VT11 brought over to Column B of Cognos? Cognos will already have Column B filled in with values from a report I will paste manually. Therefore, Column B of Cognos and Column A of VT11 will have duplicated values to make the Vlookup work.
Same logic for Column A of Cognos will have duplicated values with Column A of TietanMasterExtra sheet.

Cheers
Bruno

 

[RCBricker]

ok so really the only two things you want is to have COGNOS line items compared to VT11 line items to see if VT11 has "NW". IF not delete from COGNOS then add in the VLOOKUPs to COGNOS.

 

[RCBricker]

try this.

Sub BrunoVeiga()

Dim wb As Workbook
Dim wsC As Worksheet, wsV As Worksheet, wsT As Worksheet
Dim rngHEAD As Range, cell As Range, rng As Range, rngCOPY As Range, _
    rngCOMP1 As Range, rngCOMP2 As Range
Dim lngROWc As Long, lngCOLc As Long, lngROWv As Long, _
    lngCOLv As Long, lngROWt As Long, lngCOLt As Long
Dim intREFc As Integer, intSHIP As Integer, intCOG1 As Integer, _
    intSUP As Integer, intNAMEc As Integer, intVT1 As Integer, _
    intTEN As Integer, intVT12 As Integer, intTEI1 As Integer, _
    intADD As Integer, intREFv As Integer, intNAMEv As Integer, _
    intSHIPv As Integer, intCOMP As Integer
Dim varI As Variant

    Set wb = ThisWorkbook
    Set wsC = wb.Sheets("Cognos")
    Set wsV = wb.Sheets("VT11")
    Set wsT = wb.Sheets("TietanMasterExtra")
    With wsT
        lngROWt = wsT.Range("A" & wsT.Rows.Count).End(xlUp).Row
        lngCOLt = wsT.Cells(1, wsT.Columns.Count).End(xlToLeft).Column
        Set rngHEAD = wsT.Range(wsT.Cells(1, 1), wsT.Cells(1, lngCOLt))
        intTEI1 = rngHEAD.Find("Reference number").Column
        intADD = rngHEAD.Find("Additional Text Info").Column
    End With
    With wsC
        lngROWc = wsC.Range("A" & wsC.Rows.Count).End(xlUp).Row
        lngCOLc = wsC.Cells(1, wsC.Columns.Count).End(xlToLeft).Column
        Set rngHEAD = wsC.Range(wsC.Cells(1, 1), wsC.Cells(1, lngCOLc))
        intREFc = rngHEAD.Find("Reference Number").Column
        intSHIP = rngHEAD.Find("Shipment Number").Column
        intCOG1 = rngHEAD.Find("Order").Column
        intSUP = rngHEAD.Find("Supplement Text").Column
        intNAMEc = rngHEAD.Find("Name").Column
        With wsV
            lngROWv = wsV.Range("A" & wsV.Rows.Count).End(xlUp).Row
            lngCOLv = wsV.Cells(1, wsV.Columns.Count).End(xlToLeft).Column
            Set rngHEAD = wsV.Range(wsV.Cells(1, 1), wsV.Cells(1, lngCOLv))
            intVT1 = rngHEAD.Find("Shipment Number").Column
            Set rngCOPY = wsV.Range(wsV.Cells(2, intVT1), wsV.Cells(lngROWv, intVT1))
            intREFv = rngHEAD.Find("Document Reference").Column
            intTEN = rngHEAD.Find("Tender Status").Column
            intNAMEv = rngHEAD.Find("Name").Column
            intSHIPv = rngHEAD.Find("Shipment Number").Column

            Set rngCOMP1 = wsC.Range(wsC.Cells(2, intSHIP), wsC.Cells(lngROWc, intSHIP))
            Set rngCOMP2 = wsV.Range(wsV.Cells(1, intSHIPv), wsV.Cells(lngROWv, intSHIPv))

            For varI = lngROWc To 2 Step -1
                Set cell = wsC.Cells(varI, 2)
                intCOMP = rngCOMP2.Find(cell.Value).Row
                If wsV.Cells(intCOMP, intTEN).Value <> "NW" Then
                    cell.EntireRow.Delete
                End If
            Next
        End With
        lngROWc = wsC.Range("B" & wsC.Rows.Count).End(xlUp).Row
        Set rng = wsC.Range(wsC.Cells(2, intCOG1), wsC.Cells(lngROWc, intCOG1))
        rng.Formula = "=VLOOKUP(RC" & intSHIP & ",'" & wsV.Name & "'!R1C1:R" & lngROWv & "C" & intREFv & ",2,0)"
        Set rng = wsC.Range(wsC.Cells(2, intSUP), wsC.Cells(lngROWc, intSUP))
        rng.Formula = "=VLOOKUP(RC" & intREFc & "," & wsT.Name & "!R1C1:R" & lngROWt & "C" & intADD & ",56,0)"
        Set rng = wsC.Range(wsC.Cells(2, intNAMEc), wsC.Cells(lngROWc, intNAMEc))
        rng.Formula = "=VLOOKUP(RC" & intSHIP & ",'" & wsV.Name & "'!R1C1:R" & lngROWv & "C" & intNAMEv & ",14,0)"
    End With
End Sub

 

[BrunoVeiga]

Good morning RCBricker,

ok so really the only two things you want is to have COGNOS line items compared to VT11 line items to see if VT11 has "NW". IF not delete from COGNOS then add in the VLOOKUPs to COGNOS.

Correct. Compare COGNOS line items to VT11, to see if VT11 has "NW". IF not, delete from COGNOS then add in the VLOOKUPs to COGNOS.

 

[BrunoVeiga]

Thank you for the Code.

I was doing F8, to check each step, but nothing was happening. Even nothing happening, an error showed up: Run-time error '91': at intCOMP = rngCOMP2.Find(cell.Value).

Run-time error '91':
Object variable or with block variable not set.

For varI = lngROWc To 2 Step -1
Set cell = wsC.Cells(varI, 2)
intCOMP = rngCOMP2.Find(cell.Value).Row
If wsV.Cells(intCOMP, intTEN).Value <> "NW" Then
cell.EntireRow.Delete


Cheers,
Bruno

 

[RCBricker]

if you are using F8 to move through the code, there will be no visible activity until the FOR loop.

The error occurred because there was a line item in COGNOS that did not exist in VT11. Since it is not in VT11, do you want it deleted from COGNOS? I assume yes you do since COGNOS is to only have rows where "NW" is present on VT11.

to fix the error try

Sub BrunoVeiga()

Dim wb As Workbook
Dim wsC As Worksheet, wsV As Worksheet, wsT As Worksheet
Dim rngHEAD As Range, cell As Range, rng As Range, rngCOPY As Range, _
    rngCOMP1 As Range, rngCOMP2 As Range
Dim lngROWc As Long, lngCOLc As Long, lngROWv As Long, _
    lngCOLv As Long, lngROWt As Long, lngCOLt As Long
Dim intREFc As Integer, intSHIP As Integer, intCOG1 As Integer, _
    intSUP As Integer, intNAMEc As Integer, intVT1 As Integer, _
    intTEN As Integer, intVT12 As Integer, intTEI1 As Integer, _
    intADD As Integer, intREFv As Integer, intNAMEv As Integer, _
    intSHIPv As Integer, intCOMP As Integer
Dim varI As Variant

    Set wb = ThisWorkbook
    Set wsC = wb.Sheets("Cognos")
    Set wsV = wb.Sheets("VT11")
    Set wsT = wb.Sheets("TietanMasterExtra")
    With wsT
        lngROWt = wsT.Range("A" & wsT.Rows.Count).End(xlUp).Row
        lngCOLt = wsT.Cells(1, wsT.Columns.Count).End(xlToLeft).Column
        Set rngHEAD = wsT.Range(wsT.Cells(1, 1), wsT.Cells(1, lngCOLt))
        intTEI1 = rngHEAD.Find("Reference number").Column
        intADD = rngHEAD.Find("Additional Text Info").Column
    End With
    With wsC
        lngROWc = wsC.Range("A" & wsC.Rows.Count).End(xlUp).Row
        lngCOLc = wsC.Cells(1, wsC.Columns.Count).End(xlToLeft).Column
        Set rngHEAD = wsC.Range(wsC.Cells(1, 1), wsC.Cells(1, lngCOLc))
        intREFc = rngHEAD.Find("Reference Number").Column
        intSHIP = rngHEAD.Find("Shipment Number").Column
        intCOG1 = rngHEAD.Find("Order").Column
        intSUP = rngHEAD.Find("Supplement Text").Column
        intNAMEc = rngHEAD.Find("Name").Column
        With wsV
            lngROWv = wsV.Range("A" & wsV.Rows.Count).End(xlUp).Row
            lngCOLv = wsV.Cells(1, wsV.Columns.Count).End(xlToLeft).Column
            Set rngHEAD = wsV.Range(wsV.Cells(1, 1), wsV.Cells(1, lngCOLv))
            intVT1 = rngHEAD.Find("Shipment Number").Column
            Set rngCOPY = wsV.Range(wsV.Cells(2, intVT1), wsV.Cells(lngROWv, intVT1))
            intREFv = rngHEAD.Find("Document Reference").Column
            intTEN = rngHEAD.Find("Tender Status").Column
            intNAMEv = rngHEAD.Find("Name").Column
            intSHIPv = rngHEAD.Find("Shipment Number").Column

            Set rngCOMP1 = wsC.Range(wsC.Cells(2, intSHIP), wsC.Cells(lngROWc, intSHIP))
            Set rngCOMP2 = wsV.Range(wsV.Cells(1, intSHIPv), wsV.Cells(lngROWv, intSHIPv))

            For varI = lngROWc To 2 Step -1
                Set cell = wsC.Cells(varI, 2)
On Error Resume Next
                intCOMP = rngCOMP2.Find(cell.Value).Row
On Error GoTo 0
                If intCOMP <> 0 Then
                    If wsV.Cells(intCOMP, intTEN).Value <> "NW" Then
                        cell.EntireRow.Delete
                    End If
                Else
                    cell.EntireRow.Delete
                End If
            Next
        End With
        lngROWc = wsC.Range("B" & wsC.Rows.Count).End(xlUp).Row
        Set rng = wsC.Range(wsC.Cells(2, intCOG1), wsC.Cells(lngROWc, intCOG1))
        rng.Formula = "=VLOOKUP(RC" & intSHIP & ",'" & wsV.Name & "'!R1C1:R" & lngROWv & "C" & intREFv & ",2,0)"
        Set rng = wsC.Range(wsC.Cells(2, intSUP), wsC.Cells(lngROWc, intSUP))
        rng.Formula = "=VLOOKUP(RC" & intREFc & "," & wsT.Name & "!R1C1:R" & lngROWt & "C" & intADD & ",56,0)"
        Set rng = wsC.Range(wsC.Cells(2, intNAMEc), wsC.Cells(lngROWc, intNAMEc))
        rng.Formula = "=VLOOKUP(RC" & intSHIP & ",'" & wsV.Name & "'!R1C1:R" & lngROWv & "C" & intNAMEv & ",14,0)"
    End With
End Sub

IF YOU DO NOT WANT IT DELETED use this code

Sub BrunoVeiga()

Dim wb As Workbook
Dim wsC As Worksheet, wsV As Worksheet, wsT As Worksheet
Dim rngHEAD As Range, cell As Range, rng As Range, rngCOPY As Range, _
    rngCOMP1 As Range, rngCOMP2 As Range
Dim lngROWc As Long, lngCOLc As Long, lngROWv As Long, _
    lngCOLv As Long, lngROWt As Long, lngCOLt As Long
Dim intREFc As Integer, intSHIP As Integer, intCOG1 As Integer, _
    intSUP As Integer, intNAMEc As Integer, intVT1 As Integer, _
    intTEN As Integer, intVT12 As Integer, intTEI1 As Integer, _
    intADD As Integer, intREFv As Integer, intNAMEv As Integer, _
    intSHIPv As Integer, intCOMP As Integer
Dim varI As Variant

    Set wb = ThisWorkbook
    Set wsC = wb.Sheets("Cognos")
    Set wsV = wb.Sheets("VT11")
    Set wsT = wb.Sheets("TietanMasterExtra")
    With wsT
        lngROWt = wsT.Range("A" & wsT.Rows.Count).End(xlUp).Row
        lngCOLt = wsT.Cells(1, wsT.Columns.Count).End(xlToLeft).Column
        Set rngHEAD = wsT.Range(wsT.Cells(1, 1), wsT.Cells(1, lngCOLt))
        intTEI1 = rngHEAD.Find("Reference number").Column
        intADD = rngHEAD.Find("Additional Text Info").Column
    End With
    With wsC
        lngROWc = wsC.Range("A" & wsC.Rows.Count).End(xlUp).Row
        lngCOLc = wsC.Cells(1, wsC.Columns.Count).End(xlToLeft).Column
        Set rngHEAD = wsC.Range(wsC.Cells(1, 1), wsC.Cells(1, lngCOLc))
        intREFc = rngHEAD.Find("Reference Number").Column
        intSHIP = rngHEAD.Find("Shipment Number").Column
        intCOG1 = rngHEAD.Find("Order").Column
        intSUP = rngHEAD.Find("Supplement Text").Column
        intNAMEc = rngHEAD.Find("Name").Column
        With wsV
            lngROWv = wsV.Range("A" & wsV.Rows.Count).End(xlUp).Row
            lngCOLv = wsV.Cells(1, wsV.Columns.Count).End(xlToLeft).Column
            Set rngHEAD = wsV.Range(wsV.Cells(1, 1), wsV.Cells(1, lngCOLv))
            intVT1 = rngHEAD.Find("Shipment Number").Column
            Set rngCOPY = wsV.Range(wsV.Cells(2, intVT1), wsV.Cells(lngROWv, intVT1))
            intREFv = rngHEAD.Find("Document Reference").Column
            intTEN = rngHEAD.Find("Tender Status").Column
            intNAMEv = rngHEAD.Find("Name").Column
            intSHIPv = rngHEAD.Find("Shipment Number").Column

            Set rngCOMP1 = wsC.Range(wsC.Cells(2, intSHIP), wsC.Cells(lngROWc, intSHIP))
            Set rngCOMP2 = wsV.Range(wsV.Cells(1, intSHIPv), wsV.Cells(lngROWv, intSHIPv))

            For varI = lngROWc To 2 Step -1
                Set cell = wsC.Cells(varI, 2)
On Error Resume Next
                intCOMP = rngCOMP2.Find(cell.Value).Row
On Error GoTo 0
                If intCOMP <> 0 Then
                    If wsV.Cells(intCOMP, intTEN).Value <> "NW" Then
                        cell.EntireRow.Delete
                    End If
            Next
        End With
        lngROWc = wsC.Range("B" & wsC.Rows.Count).End(xlUp).Row
        Set rng = wsC.Range(wsC.Cells(2, intCOG1), wsC.Cells(lngROWc, intCOG1))
        rng.Formula = "=VLOOKUP(RC" & intSHIP & ",'" & wsV.Name & "'!R1C1:R" & lngROWv & "C" & intREFv & ",2,0)"
        Set rng = wsC.Range(wsC.Cells(2, intSUP), wsC.Cells(lngROWc, intSUP))
        rng.Formula = "=VLOOKUP(RC" & intREFc & "," & wsT.Name & "!R1C1:R" & lngROWt & "C" & intADD & ",56,0)"
        Set rng = wsC.Range(wsC.Cells(2, intNAMEc), wsC.Cells(lngROWc, intNAMEc))
        rng.Formula = "=VLOOKUP(RC" & intSHIP & ",'" & wsV.Name & "'!R1C1:R" & lngROWv & "C" & intNAMEv & ",14,0)"
    End With
End Sub

 

[BrunoVeiga]

Hi RCBricker,

I tried this morning and seems to be working beautifully!! I will carefully check this afternoon.
Thank you so much. I dont know what is the common practice here, how can I repay your support and good will

Cheers
Bruno