Working with different sheets

[Guinaba]

Hi guys,

Wondering if you someone could help me out:

Scenario:

Two sheets:

Sheets(1) = OutputSheet
Sheets(3) = SourceSheet



I am using WorksheetFunction.Vlookup to look-up the rows of Sheets(1) Col.B into Sheets(3) Col. The row from Sheet(1) Col.B is concatenate with a variable called GetPeriod. For example:

Getperiod = 01.01.2020

Row Value = Bang

Result: Bang_1/01/2020

Using the Result Bang_1/01/2020 look up to Sheets(3) Col. A and if it exists, then count how many times this value is showing in the Sheets(3) Col. A

Col.A

Bang_1/01/2020

Bang_1/02/2020

So the result showing in Sheets(1). Col.B should be 2

Col. B

2

I am doing a nested loop to get the repeated items from sheet3 but even specifying the Sheet(3).Cells(i,1) I cannot get i ?

I am getting lost between the two sheets!!! Any help please!



Here is the code:

Option Explicit


Public Function GetPeriod() As Variant


    GetPeriod = ThisWorkbook.Sheets(1).Range("a2").Value 'Variable to keep the period for analysis


End Function



Sub GetValueFrom ()


    Dim SourceLastRow As Long

    Dim OutputLastRow As Long

    Dim SourceSheet As Worksheet

    Dim OutputSheet As Worksheet

    Dim i As Integer, r As Integer, VLK As Variant

    Dim Cell As Range


    'Worksheet names:


    Set SourceSheet = Worksheets(3)

    Set OutputSheet = Worksheets(1)


 

    'Determine last row of source


    With SourceSheet


        SourceLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row


    End With



    With OutputSheet


      OutputLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row  'Determine last row in col OutPutSheet


         r = 6 'Vlookup row start at 6 position


         .Range("D6:D" & OutputLastRow).ClearContents 'Deleting previous records everytime the variable  GetPeriod is changed


      

      For Each Cell In .Range("B6:B" & OutputLastRow) 'Looping through Outputsheet


           On Error Resume Next ' if value is not found to avoid error 1004

    


           With SourceSheet


               For i = 2 To .Range("A" & SourceLastRow)                  'Looping through SourceSheet to count values


                  If .Cells(i, 1).Value <> .Cells(i + 1, 1).Value Then        'if row n is different from (n+1) count


                    VLK = WorksheetFunction.Vlookup(Cells(r, 2) & "_" & GetPeriod, Sheets(3). _


                     Range("a1:a" & SourceLastRow).Value, 1, 0)


                     .Cells(r, 4).Value = WorksheetFunction.Count(VLK)        'write the value in Outputsheet


                 Else


                    VLK = VLK + 1 ' if row n is igual to (n+1) accumulate value before writing it to Outputsheet


                     i = i + 1


                  End If


               Next


             End With


                   r = r + 1 'Move to the next role in Outputsheet


        Next


    End With


End Sub

 

[Guinaba]

Hi @DanteAmor,

Sorry to bug you again with the same question, but wondering if you could help me out, your code works perfectly with the example that I gave you, my mistake was not to give you the exact sheet format that I have, so I am trying to adjust your code to my original sheet but without success .

In my original file I have to join two ranges: A and B in sheet1 like you did in yours, however range B is just one row (B2):

Range A: sheets(1) Syspro Supplier Code AVI02 PRI05 KLX01 KLX02 QUA03 LAM02 PPG01 HEX01 CYT04 ALC01 INT04 LOV01 AER03 FOR02 WES02 APP05 HEN01 INT07 INT08 RUB01 TEN02 CEM01

Range B: sheets(1) Jan-20

After joining these two ranges in sheet1 I can follow your logic using the Countif with the 3rd range in sheet3:

Range C in sheets(3)

Lookup Value
APP05_1/02/2020
AVI02_1/01/2020
AVI02_1/02/2020
AVI02_1/02/2020
AVI02_1/02/2020
CEM01_1/02/2020
INT07_1/02/2020
KLX02_1/01/2020
KLX02_1/01/2020
KLX02_1/01/2020
KLX02_1/01/2020
KLX02_1/01/2020
LAM02_1/01/2020
LOV01_1/02/2020
RUB01_1/02/2020

Could you please help me to adjust your sub to capture this scenario?

Here is what I have tried:

Sub CountValues()

Dim Range_A As Range
Dim Range_B As Range
Dim Range_C As Range 'Union btw A and B
Dim LastRow As Long

LastRow = Sheets(1).Range("B6:B" & Rows.Count).End(xlDown).row

Set Range_A = Sheets(1).Range("B6:B" & LastRow)
Set Range_B = Sheets(1).Range("A2")

Range_C = Union(Range_A, Range_B).Select

  With Range_C
    .Formula = "=COUNTIF(Sheet3!B:B,Range_C&""*"")"
    .Value = .Value
  End With
  
End Sub

 

[DanteAmor]

Try this:

Sub CountValues()
  With Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Range("A" & Rows.Count).End(3).Row)
    .Formula = "=COUNTIF(Sheet3!C:C,A2&""_""&TEXT($B$2,""d/mm/yyyy""))"
    .Value = .Value
  End With
End Sub

Results:

Dante Amor
	A	B	C
1	Syspro Supplier Code	Date	Result
2	AVI02	01-ene	1
3	PRI05		0
4	KLX01		0
5	KLX02		5
6	QUA03		0
7	LAM02		1
Sheet1

Dante Amor
	A	B	C
1			Lookup Value
2			APP05_1/02/2020
3			AVI02_1/01/2020
4			AVI02_1/02/2020
5			AVI02_1/02/2020
6			AVI02_1/02/2020
7			CEM01_1/02/2020
8			INT07_1/02/2020
9			KLX02_1/01/2020
10			KLX02_1/01/2020
11			KLX02_1/01/2020
12			KLX02_1/01/2020
13			KLX02_1/01/2020
14			LAM02_1/01/2020
15			LOV01_1/02/2020
16			RUB01_1/02/2020
Sheet3

 

[Guinaba]

Not sure what I am missing @DanteAmor I cross-checked everything but the result is coming zero

Sheet 1 - Col A, B, C Supplier Code Date Result AVI02 1/01/2019 0 PRI05 0 KLX01 0 KLX02 0 QUA03 0 LAM02 0 PPG01 0 HEX01 0 CYT04 0 ALC01 0 INT04 0 LOV01 0 AER03 0 FOR02 0 WES02 0 APP05 0 HEN01 0 INT07 0 INT08 0 RUB01 0 TEN02 0 CEM01 0

Sheet 3 - Col.C Lookup Value APP05_1/02/2020 AVI02_1/01/2020 AVI02_1/01/2020 AVI02_1/01/2020 AVI02_1/01/2020 CEM01_1/02/2020 INT07_1/02/2020 KLX02_1/01/2020 KLX02_1/01/2020 KLX02_1/01/2020 KLX02_1/01/2020 KLX02_1/01/2020 LAM02_1/01/2020 LOV01_1/02/2020 RUB01_1/02/2020

Sub CountValues()
  With Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Range("A" & Rows.Count).End(3).Row)
    .Formula = "=COUNTIF(Sheet3!C:C,A2 & ""_"" & TEXT($B$2,""d/mm/yyyy""))"
    .Value = .Value
  End With
End Sub

 

[Guinaba]

@DanteAmor could you please send your file to me? gnbsap@gmail.com
Cheers, Gilly

 

[DanteAmor]

On sheet1 you have 2019.
On sheet3 you have 2020.

 

[Guinaba]

OMG!!! ! Didn't see that!! Awesome! It is working now @DanteAmor! You are legend! Thanks again!

 

[DanteAmor]

Glad we could help & thanks for the feedback