VBA Find Cell in Row Based on Active Cell Value

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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:

Code:
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
 
Upvote 0
Note that in your search command, you are telling it to search "Cells", which is every cell on the entire sheet:
Code:
    [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:
Code:
    [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
 
Upvote 0
Note that in your search command, you are telling it to search "Cells", which is every cell on the entire sheet:
Code:
    [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:
Code:
    [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!!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top