Forum Discussion

mark ainscough's avatar
mark ainscough
Brass Contributor
Jul 22, 2018

If cell value contains specific text do nothing any other value copy cell

I am using the formula below which works fine but I need to add to it repeating the process a few times for different cells (17 cells in total)

 

If a cell H20 on worksheet "Estimate Parts Lookup" contains the text "Non Found" then the no change is made to cell E49 on worksheet "Estimate" If H20 on worksheet "Estimate Parts Lookup" contains any value other than "Non Found" the value is copied to cell E49 on worksheet "Estimate"

 

Sub Copypartsprices()

With Sheets("Estimate")

If Sheets("Estimate Parts lookup").Range("H20") = "Non found" Then
 End
 End If
Range("E49").Value = Sheets("Estimate Parts lookup").Range("L2")

End With

End Sub

 I now want to repeat the process above but adding to the process so that as well as the above it also checks to see if cell H21 on worksheet "Estimate Parts Lookup" contains the text "Non Found"as above if it does I want to make no change to cell E50 but again If H21 on worksheet "Estimate Parts Lookup" contains any value other than "Non Found" the value is copied to cell E50 on worksheet "Estimate"

 

The below does not work but is what I have so far!

 

Sub Copypartsprices()

With Sheets("Estimate")

If Sheets("Estimate Parts lookup").Range("H20") = "Non found" Then
 End
 End If
Range("E49").Value = Sheets("Estimate Parts lookup").Range("L2")

If Sheets("Estimate Parts lookup").Range("H21") = "Non found" Then
 End
 End If
Range("E50").Value = Sheets("Estimate Parts lookup").Range("L3")

End With
End Sub

 In all I will repeat this process 17 times as below. Hope this makes sense

 

E49 =H20 Unless H20 ="Non Found"

E50 =H21 Unless H21 ="Non Found"

E51 =H22 Unless H22 ="Non Found"

E52 =H23 Unless H23 ="Non Found"

E53 =H24 Unless H24 ="Non Found"

E54 =H25 Unless H25 ="Non Found"

E55 =H26 Unless H26 ="Non Found"

E56 =H27 Unless H27 ="Non Found"

E57 =H28 Unless H28 ="Non Found"

E58 =H29 Unless H29 ="Non Found"

E59 =H30 Unless H30 ="Non Found"

J49 =H31 Unless H31 ="Non Found"

J50 =H32 Unless H32 ="Non Found"

J51 =H33 Unless H33 ="Non Found"

J52 =H34 Unless H34 ="Non Found"

J53 =H35 Unless H35 ="Non Found"

J54 =H36 Unless H36 ="Non Found"

 

Thanks in advanced Mark

No RepliesBe the first to reply

Resources