find text in cell

[pedy]

Hi all,

I need a macro that will find a cell by its contents (text) and then it will add the value of 1 to the cell under it IF the is data in the "E" cell of the same row. I would also need for it to do the same (add +1) for every other "A" cell under it.

Any help is appreciated

Pedy

 

[brian.wethington]

I need a macro that will find a cell by its contents (text)

Will that be in a certain range? And what would that text that you are looking for be?

then it will add the value of 1 to the cell under it IF the is data in the "E" cell of the same row

So you need to find the cell then the row of that cell, and check E[Row] and if it is a date (any date) then you need to add 1 to the cell you found?

I would also need for it to do the same (add +1) for every other "A" cell under it.

Then you will need to go from the row below the row of the cell you found and add one to every cell in A.


Is this about right?

 

[pedy]

Hi Brian,

1 - I guess the range is in column A but it could look in the whole worksheet.

2 - EX.: The text found is in cell A6. It will now add the value 1 to A7 but only if there is any kind of data in cell E7 (same row as A7).

3 - It will keep looking fo data in the E column and addind +1.

Ex.: A7 = 1
A8 = A7+1
A9 = A8+1
etc.

the reason I'm doing it like this is that there's another macro that will be adding a full row depending on a condition. This row is being added from row 4 and up but the text that I'm trying to locate will always be moving down so I cannot reference an exact cell #...

Thanks

 

[bradh_nz]

Try this thread
http://www.mrexcel.com/board2/viewtopic.php?t=246671&highlight=

 

[pedy]

Thanks bradh_nz

...but I'm not sure that it applies...is that the right thread ?

What I need is for the macro to find (for instance) the text "Item No.:" and whe it has found that text to start adding values in increments of 1 in the foloowing cells (cells under the found text) but only if there is data in the colunm E of the same row as the cell...

Thanks

Pedy

 

[brian.wethington]

I am still not completely sure of what you are needing. So you find the text you were searching for in A6 and so you check E7 and if there is anything in E7 then you put a 1 in A7? Will you then check E8 to determine if you want to put 2 in A8?

If the following is relevant then I am absolutely missing something, what would it be?

Try this thread
http://www.mrexcel.com/board2/viewtopic.php?t=246671&highlight=

Doesn't this have to do with parsing of cells? I don't believe this is what the OP is doing.

 

[brian.wethington]

I think your last post cleared it up a bit for me... does something like this work?

Sub CmptlyUntested()
Dim FndRw As Long, c As Range, LstRw As Long

'Did not sound like you really wanted to keep it only to column A but if you
'want to you can put Range("A:A") in place of Cells in the following line
Set c = Cells.Find(What:="Item No.", After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'This is currently looking for anything, change the "*" to whatever you would be looking for.

If Not c Is Nothing Then
    FndRw = c.Row
    If IsDate(Cells(FndRw + 1, "E")) Then
        LstRw = Cells(Rows.Count, "E").End(xlUp).Row
        For x = FndRw + 1 To LstRw
            If IsDate(Cells(x, "E")) Then
                Cells(x, "A").Value = x - FndRw
            End If
        Next
    End If
End If

End Sub

 

[pedy]

Hi Brian,

Yes that kind of seem to work but it's looking for a "datE" in the "E" cells. I need it to validate if there is any kind of "datA" (text, numbers, etc.).

Pedy

 

[brian.wethington]

LOL, sorry about that I thought you were looking for a date the whole time. Does this work?

Sub CmptlyUntested() 
Dim FndRw As Long, c As Range, LstRw As Long 

'Did not sound like you really wanted to keep it only to column A but if you 
'want to you can put Range("A:A") in place of Cells in the following line 
Set c = Cells.Find(What:="Item No.", After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
'This is currently looking for anything, change the "*" to whatever you would be looking for. 

If Not c Is Nothing Then 
    FndRw = c.Row 
    If IsDate(Cells(FndRw + 1, "E")) Then 
        LstRw = Cells(Rows.Count, "E").End(xlUp).Row 
        For x = FndRw + 1 To LstRw 
            If Len(Cells(x, "E")) <> 0 Then 
                Cells(x, "A").Value = x - FndRw 
            End If 
        Next 
    End If 
End If 

End Sub

 

[pedy]

That it thanks !!!

But what if a user decides to remove the contents of 1 or more "E" cells ? Will the referenced "A" cell be cleared ?

Pedy