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

 

[CountTepes]

If I am not misunderstanding what you want.

The quick way to count instances of a value would be using the CountIf function.

so in your instance using the value Bang_1/01/2020 you could use

Result= CountIf(Worksheets(3).Range("A:A"),"Bang_1/01/2020")

But if you want to look at a list in a range and count the occurrences of each value in another sheet, you can do it like this after you have created your ranges

For each S in OutputSheetRange
    Result= CountIf(Worksheets(3).Range("A:A"),S)
    S.Offset(0,1)=result ' This will put the result next to the value you were using as a search value
Next

 

[Guinaba]

Thanks @CountTepes for your feedback,

I've tried countif, but didn't work, not sure what I am doing wrong, the condition in my Countif is to count repetitive cells within the range

With SourceSheet

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

Result = WorksheetFunction.CountIf(Range("A1:A" & SourceLastRow), .Cells(i, 1).Value <> .Cells(i + 1, 1).Value)

Result = Result + 1

Sheets(1).Cells(r, 4).Value = Result

i = i + 1

Next

End With

 

[DanteAmor]

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

You could give another example. In your previous example it only appears once.
So I'm confused, it must be 1 or 2. Why 2?

Forget your formulas and macros.
Just give an example of what you have on sheet1, what you have on sheet3 and the expected result.

Use XL2BB tool.

 

[Guinaba]

Hi @DanteAmor,

Hope this make my logic clearer...

Sheet(1)

The vba code is concatenating the values from Supplier Code col with Get Period Variable: 1/1/2020 , resulting in AVI02_1/1/2020, PRI05_1/1/2020, etc

Supplier Code	Column to Paste the result of sheet(3)
AVI02
PRI05
KLX01
KLX02
QUA03
LAM02
PPG01
HEX01
CYT04
ALC01
INT04
LOV01
AER03
FOR02
WES02
APP05
HEN01
INT07
INT08
RUB01
TEN02
CEM01

Sheet(3):

The VBA code has to count how many times each rows shows in Sheet(3) is showing and return the result to Sheet(1), ex. AVI02_1/1/2020 is showing 2x, AVI02_1/1/2020 5x, etc

Index

AVI02_1/01/2020

AVI02_1/02/2020

CEM01_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

Sorry couldn't install the add-in XL2BB in my work laptop.

 

[DanteAmor]

A cycle is not necessary and the vlookup function is not necessary.

With the following you can count:
Change Sheet1 and Sheet3 by the name of your sheets

Sub CountValues()
  With Sheets("Sheet1").Range("B2:B" & Sheets("Sheet1").Range("A" & Rows.Count).End(3).Row)
    .Formula = "=COUNTIF(Sheet3!B:B,A2&""*"")"
    .Value = .Value
  End With
End Sub

Result:

varios 16feb2020.xlsm
	A	B
1	Supplier Code	Column to Paste the result of sheet(3)
2	AVI02	2
3	PRI05	0
4	KLX01	0
5	KLX02	5
6	QUA03	0
7	LAM02	1
8	PPG01	0
9	HEX01	0
10	CYT04	0
11	ALC01	0
12	INT04	0
13	LOV01	1
14	AER03	0
15	FOR02	0
16	WES02	0
17	APP05	0
18	HEN01	0
19	INT07	0
20	INT08	0
21	RUB01	1
22	TEN02	0
23	CEM01	1
Sheet1

 

[Guinaba]

Thanks @DanteAmor! That is awesome!
Do you mind explaining your logic using:

.Formula = "=COUNTIF(Sheet3!B:B,A2&""*"")"
.Value = .Value

 

[DanteAmor]

What the macro does is put the formula in the range of cells

With Sheets("Sheet1").Range("B2:B" & Sheets("Sheet1").Range("A" & Rows.Count).End(3).Row)

Put this formula
.Formula = "=COUNTIF(Sheet3!B:B,A2&""*"")"

The formula means counting on sheet 3 in column A everything that starts with A2 (A2 and the wildcard *)

And the following means that you convert the formula to its result, that is, in the cell you will no longer see the formula, you only see the result.
.Value = .Value

If you run the macro without this line .Value = .Value you will see the formula in the cells.

 

[Guinaba]

Gracias @DanteAmor! Much appreciated!!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback