find text in cell

pedy

Board Regular
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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

brian.wethington

Well-known Member
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.

pedy

Board Regular
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

pedy

Board Regular

...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

Well-known Member
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?

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

brian.wethington

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

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

Board Regular
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

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

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

Board Regular
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

Replies
31
Views
503
Replies
3
Views
131
Replies
4
Views
127
Replies
4
Views
156
Replies
1
Views
287

1,191,200
Messages
5,985,238
Members
439,953
Latest member
suchitha

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.

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

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