Guinaba
Board Regular
- Joined
- Sep 19, 2018
- Messages
- 218
- Office Version
- 2016
- Platform
- Windows
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:
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:
VBA 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
Last edited by a moderator: