find value and eneter date in a different cell

JamRivera

New Member
Joined
Jun 9, 2019
Messages
34
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?
 
Upvote 0
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
 
Upvote 0
Hello JamRivera,

Here is the macro without using a For Next loop.

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
Hello JamRivera,

Sorry, here is the corrected code...
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
Rich (BB code):
        ' // Last cell in column with data.
        LastRow = Wks.Cells(Rows.Count, "C").End(xlUp).Row
 
Upvote 0
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 :)
 
Upvote 0
Hello JamRivera,

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

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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