Forum Discussion
Data Extraction
Hi, i hope that I have understood the task. You want to check if a date is on monday to friday?
I write an userdefined function, see attached file, too.
Function commonDays(strYear As String, strMonth As String, strDay) As Boolean
Dim dteDate As Date
dteDate = DateSerial(strYear, strMonth, strDay)
If Application.WorksheetFunction.Weekday(dteDate, vbMonday) < 6 Then
commonDays = True
Else
commonDays = False
End If
End FunctionRegards from germany
Bernd
http://www.vba-Tanker.com
Hi Bernd,
Thank you to your prompt response to my request. The function that you defined is informative but actually i want to extract the numbers of days that are found common among the four data sheets in excel.
Likewise;
Sheet-1 contain days 24,25,26, for April, 1979
Sheet-2 only contain 28th day of April, 1979 while;
Sheet-3 and 4 does not contain any number of days in April, 1979 so we can say that we do not found common numbers of days in April, 1979.
Whereas;
Sheet-1,2,3 and 4 all contain the days of 18,19 for the month of June, 1979 as common number of days.
These common days are also highlighted with yellow color in the attached excel sheet, thanks
Regards
Farhan
Hi Farhan,
ok, let's take a look at the attached file. These days who are common in every sheet are colored green in column A.
Sub CommonDaysInAllSheets()
Dim lngCol As Long
Dim lngRow As Long
Dim lngRowMax As Long
Dim rngFind1 As Range
Dim rngFind2 As Range
Dim rngFind3 As Range
With Tabelle1
lngRowMax = .Range("A" & .Rows.Count).End(xlUp).Row
For lngCol = 4 To 17 Step 4
For lngRow = 3 To lngRowMax
.Cells(lngRow, lngCol).Value = _
DateSerial(.Cells(lngRow, lngCol - 3).Value, .Cells(lngRow, lngCol - 2).Value, .Cells(lngRow, lngCol - 1).Value)
Next lngRow
Next lngCol
For lngRow = 3 To lngRowMax
Set rngFind1 = .Range("H:H").Find(what:=.Cells(lngRow, "D").Value, lookat:=xlWhole)
If Not rngFind1 Is Nothing Then
Set rngFind2 = .Range("L:L").Find(what:=.Cells(lngRow, "D").Value, lookat:=xlWhole)
If Not rngFind2 Is Nothing Then
Set rngFind3 = .Range("P:P").Find(what:=.Cells(lngRow, "D").Value, lookat:=xlWhole)
If Not rngFind3 Is Nothing Then
.Cells(lngRow, "D").Interior.ColorIndex = 4
End If
End If
End If
Next lngRow
End With
End Sub
Regards
Bernd
http://www.vba-tanker.com
Hi Bernd,
Yes, man this is exactly i want to extract the common numbers of days from multiple sheets. Thank you for the help and guidance on it.
Farhan