I have the below code that is currently looking up specific cells
FindDate As Date with the Value being "E8" however, this is for Monday only. I want to be able to find all of the days and input their values into the required sheet.
Tuesday = I8
Wednesday = M8
Thursday = Q8
Friday = U8
Saturday = Y8
Sunday = AC8
FindDate As Date with the Value being "E8" however, this is for Monday only. I want to be able to find all of the days and input their values into the required sheet.
Tuesday = I8
Wednesday = M8
Thursday = Q8
Friday = U8
Saturday = Y8
Sunday = AC8
Code:
'Sub FindMon(Day As Long, Name As String)
Sub FindMon()
Dim FindDate As Date
Dim FindDt As Date
Dim FindNm As String
Dim Rng As Range
Dim SelRange As Range
FindDate = Worksheets("Feasibility Other").Range("E8").Value
FindNm = Worksheets("Feasibility Other").Range("B10").Value
Worksheets("Feasibility Other").Visible = -1
Worksheets("Analyst Data").Visible = -1
'Monday
'Find Name
With Sheets("Analyst Data").Range("CN2:CN13141")
Set Rng = .Find(What:=FindNm, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
ActiveCell.Offset(-1, 1).Select
Range(ActiveCell, Selection.End(xlDown)).Select
Set SelRange = Selection
'Find Date
With SelRange
Set Rng = .Find(What:=FindDate, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Worksheets("Feasibility Other").Range("F10").Value
ActiveCell.Offset(0, 1).Value = Worksheets("Feasibility Other").Range("G10").Value
Worksheets("Feasibility Other").Activate
Worksheets("Analyst Data").Visible = 2
End Sub