VBA Find Cell in Row Based on Active Cell Value

[Finalfight40]

Hi All

I am just having some trouble with a few lines in a macro i am trying to create.

Currently i am in an active cell on one of my tabs which is a date. I would like to use this date to find a matching date in another sheet in the currently active row.

For example if i am in row 43 on my second sheet, it will only look at row 43 for the date that matches my currently active cell on the first sheet and go to it. How could i do this because i have multiple dates on the same sheet but the dates in the row i am looking up would be unique?

Thanks in Advance

 

[Finalfight40]

Actually i figured out the majority of it but is there a way to only search on active row instead of looking at the whole sheet?

 

[Joe4]

Please post the code as you have it now, so we can see how you are searching, and we can help you make the edit to do what you wan.

 

[Finalfight40]

Please post the code as you have it now, so we can see how you are searching, and we can help you make the edit to do what you wan.

Thank you Joe.

Please see below:

Sub Project_Summary()
'
' Project_Summary Macro
'


'
Dim ProjectMinDate As Long
Dim SummaryMinDate As Long
Dim NumofColumns As Long
Dim SummaryMaxDate As Long
Dim ProjectMaxDate As Long
Dim DaysofWork As Long
Dim ArtistsNeeded As Long
Sheets(3).Activate
    Cells.Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    SummaryMinDate = ActiveCell.Value
    Selection.End(xlToRight).Select
    SummaryMaxDate = ActiveCell.Value
    For i = 4 To Sheets.Count
    Sheets(i).Activate
    Cells.Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    ProjectMinDate = ActiveCell.Value
    If ProjectMinDate < SummaryMinDate Then
    SummaryMinDate = ProjectMinDate
    End If
    Selection.End(xlToRight).Select
    ProjectMaxDate = ActiveCell.Value
    If ProjectMaxDate > SummaryMaxDate Then
    SummaryMaxDate = ProjectMaxDate
    End If
    Next
    Sheets(2).Activate
        Cells.Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    NumofColumns = ((SummaryMaxDate - SummaryMinDate) / 7) - 1
    For J = 1 To NumofColumns + 2
    ActiveCell.Value = SummaryMinDate
    Selection.NumberFormat = "m/d/yyyy"
    Set LookUpDate = ActiveCell
        For k = 3 To Sheets.Count
        Sheets(k).Activate
            Cells.Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
                ActiveCell.Rows("1:1").EntireRow.Select
                Cells.Find(What:=LookUpDate).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            DaysofWork = DaysofWork + ActiveCell.Value
            ActiveCell.Offset(4, 0).Range("A1").Select
            ArtistsNeeded = ArtistsNeeded + ActiveCell.Value
        Next
            Sheets(2).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = DaysofWork
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveCell.Value = ArtistsNeeded
            ActiveCell.Offset(-2, 1).Range("A1").Select
    
    SummaryMinDate = SummaryMinDate + 7
        
    
    Next
    
    
End Sub

It is still a work in progress.

However the bit i having trouble with would be:

Cells.Find(What:=LookUpDate).Activate

I would like to only search for the LookUpDate on the currently active row as i have this date pasted in multiple places on each sheet but it would be unique on this row.

Thank You

 

[Joe4]

Note that in your search command, you are telling it to search "Cells", which is every cell on the entire sheet:

    [COLOR=#ff0000]Cells[/COLOR].Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If you want it to just search the Active row, change it like this:

    [COLOR=#ff0000]Rows(ActiveCell.Row)[/COLOR].Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

 

[Finalfight40]

Note that in your search command, you are telling it to search "Cells", which is every cell on the entire sheet:

    [COLOR=#ff0000]Cells[/COLOR].Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If you want it to just search the Active row, change it like this:

    [COLOR=#ff0000]Rows(ActiveCell.Row)[/COLOR].Find(What:="2D Start Date Week Commencing", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

Wow i never noticed that. I pulled this from the macro recorder as you can probably tell.

I will definitely remember this and thank you!!

 

[Joe4]

You are welcome!

The Macro Recorder is great, but is also very literal. So sometimes you need to make small tweaks to the code to make it more dynamic, or behave a little differently.

 

[Finalfight40]

I was trying to experiment a lot with the "after:=" section thinking this might be the solution but i never noticed the part you pointed out to me.

 

[Joe4]

I was trying to experiment a lot with the "after:=" section thinking this might be the solution but i never noticed the part you pointed out to me.

Yes, "after" is the starting point within whatever search range it is being applied to. But we needed to first define that search range.

 

[Finalfight40]

I See.

Thanks for the explanation.