find value and eneter date in a different cell

[JamRivera]

Hello, I'm stuck and need help please help me I need the following macro to work across 35 tabs after tab7

Sub FDADATE2()

Dim SrchRng As Range, cel As Range
Set SrchRng = Range("A:C")
For Each cel In SrchRng
If InStr(1, cel.Value, "FDA") > 0 Then
cel.Offset(0, 2).Value = "12312019"
End If
Next cel

End Sub

 

[Leith Ross]

Hello JamRivera,

You realize that if you offset 2 columns to the right of "A" you will overwrite what is column "C". Is that correct?

 

[JamRivera]

Yes but what I really need is for the macro to look under column C and offset by 2 entering he data12019 into column E

 

[Leith Ross]

Hello JamRivera,

Here is the macro without using a For Next loop.

Sub FDADATE2()


    Dim Cell    As Range
    Dim LastRow As Long
    Dim SrchRng As Range
    
        ' // Start cell in column "C". Does not have to be C1.
        Set SrchRng = Range("C1")
        
        ' // Last cell in column with data.
        LastRow = SrchRng.Cells(Rows.Count, "C").End(xlUp)
        
        ' // If the lastrow is lower than the starting row there is no data.
        If LastRow < SrchRng.Row Then Exit Sub
        
        ' // Set the search range to the cells of interest.
        Set SrchRng = SrchRng.Resize(LastRow - SrchRng.Row + 1, 1)
        
        ' // Search for the value "FDA" using partial matching.
        Set Cell = SrchRng.Find("FDA", , xlValues, xlPart, xlByRows, xlNext, False, False, False)
        
        ' // Nothing is returned if search fails.
        If Not Cell Is Nothing Then Cell.Offset(0, 2).Value = "12312019"


End Sub

 

[JamRivera]

Hi Leith, I apologize I wasn't clear in my request because I was rushing
I need the macro to look under column C for the text FDA then offset by 2 and enter the date 12312019 under column E
Also ran your macro but got a compile error

Hello JamRivera,

Here is the macro without using a For Next loop.

Sub FDADATE2()


    Dim Cell    As Range
    Dim LastRow As Long
    Dim SrchRng As Range
    
        ' // Start cell in column "C". Does not have to be C1.
        Set SrchRng = Range("C1")
        
        ' // Last cell in column with data.
        LastRow = SrchRng.Cells(Rows.Count, "C").End(xlUp)  <<<compile error 13 type mismatch 
        
        ' // If the lastrow is lower than the starting row there is no data.
        If LastRow < SrchRng.Row Then Exit Sub
        
        ' // Set the search range to the cells of interest.
        Set SrchRng = SrchRng.Resize(LastRow - SrchRng.Row + 1, 1)
        
        ' // Search for the value "FDA" using partial matching.
        Set Cell = SrchRng.Find("FDA", , xlValues, xlPart, xlByRows, xlNext, False, False, False)
        
        ' // Nothing is returned if search fails.
        If Not Cell Is Nothing Then Cell.Offset(0, 2).Value = "12312019"


End Sub

 

[Leith Ross]

Hello JamRivera,

Sorry, here is the corrected code...

Sub FDADATE2()


    Dim Cell    As Range
    Dim LastRow As Long
    Dim SrchRng As Range
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
        
        ' // Start cell in column "C". Does not have to be C1.
        Set SrchRng = Wks.Range("C1")
        
        ' // Last cell in column with data.
        LastRow = Wks.Cells(Rows.Count, "C").End(xlUp)
        
        ' // If the lastrow is lower than the starting row there is no data.
        If LastRow < SrchRng.Row Then Exit Sub
        
        ' // Set the search range to the cells of interest.
        Set SrchRng = SrchRng.Resize(LastRow - SrchRng.Row + 1, 1)
        
        ' // Search for the value "FDA" using partial matching.
        Set Cell = SrchRng.Find("FDA", , xlValues, xlPart, xlByRows, xlNext, False, False, False)
        
        ' // Nothing is returned if search fails.
        If Not Cell Is Nothing Then Cell.Offset(0, 2).Value = "12312019"


End Sub

 

[JamRivera]

Hi Leith, Thanks for all the help REALLY appreciate it a lot.
Ran your macro but still getting the same error
Sub FDADATE2()


Dim Cell As Range
Dim LastRow As Long
Dim SrchRng As Range
Dim Wks As Worksheet

Set Wks = ActiveSheet

' // Start cell in column "C". Does not have to be C1.
Set SrchRng = Wks.Range("C1")

' // Last cell in column with data.
LastRow = Wks.Cells(Rows.Count, "C").End(xlUp) compile error

' // If the lastrow is lower than the starting row there is no data.
If LastRow < SrchRng.Row Then Exit Sub

' // Set the search range to the cells of interest.
Set SrchRng = SrchRng.Resize(LastRow - SrchRng.Row + 1, 1)

' // Search for the value "FDA" using partial matching.
Set Cell = SrchRng.Find("FDA", , xlValues, xlPart, xlByRows, xlNext, False, False, False)

' // Nothing is returned if search fails.
If Not Cell Is Nothing Then Cell.Offset(0, 2).Value = "12312019"


End Sub

 

[Leith Ross]

Hello JamRivera,

I just back from doing some invoicing. I am not with it today. The compile error was due to trying to assign a object to a long integer. The line correction is below.

        ' // Last cell in column with data.
        LastRow = Wks.Cells(Rows.Count, "C").End(xlUp).Row

 

[JamRivera]

Hi Leith, Sorry for the delayed response I have been crazy busy at work.
I tested this and it works but I need two things
I have approx. 35 tabs so I need the macro to check from tab 7 and on FDA
also each tab has FDA listed several times so I need the macro to enter the date for each time it finds FDA.

Also appreciate all your help Thank You so much

 

[Leith Ross]

Hello JamRivera,

Can you post a screen shot of a worksheet with the multiple FDA listings? Thanks.