Macro searches for keyword. If found , puts 0 in cell from next co

A

andrei

Example

A1 : available in 10 days ...
A2 : unavailable
A3 : available in 4 days
A4 : unavailable

B1 : 10.30
B2 : 5.30
B3 : 13.89
B4 : 5.48

Tha macro should search for "unavailable" . It finds the keyword in A2 and
A4 . So , it changes content from the B2 and B4 cells to 0

B1 : 10.30
B2 : 0
B3 : 13.89
B4 : 0
 
M

Mike H

Hi,

Right click your sheet tab, view code and paste the code in and run it

Sub stance()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "UNAVAILABLE" Then
c.Offset(, 1) = 0
End If
Next
End Sub

Mike
 
J

Jacob Skaria

Sub ReFormat()
For Each c In Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
If InStr(1, c.Text, "UnAvailable", vbTextCompare) Then c.Offset(, 1) = 0
Next
End Sub

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro - searches for key word in a column and concatenates entire 6
Macro for Creating Word Document!! 5
use macro to copy formula with value and not formula without value 3
Macro that splits content from cell if given character is found 4
Macro - reads cells in a column .If keyword found moves cell conte 5
Macro that analyses data from 3 columns and puts result in 4th 2
macro which delets cell if the cell above has the same keyword 2
Macro that analyses 3 columns and put results in 4th 5

Top