So the code I have basically does what I want it to do: I have hundreds of Excel files I need to modify at a time repeatedly. If a specific cell ("B1") has the word string "draw" in it, nothing is to happen. If the cell doesn't have the word string "draw", the word "tank" is to be inserted before the word "prep" in the cell. The macro runs through all the files in a given folder, changes the format, outputs to a new folder, etc. This all works beautifully. But on occasion, the cell may contain the word string "pool" instead of "draw". In that case, I don't want to change the cell contents at all. So basically, if "pool" or "draw" is in the cell, do nothing. If they're both not present, add "Tank" before the word string "prep" in the cell. Here's the code I have:
VBA Code:
Sub SIS_ALIMS()
Dim wbOpen As Workbook
Dim MyDir As String
MyDir = "C:\Processed data"
strExtension = Dir(MyDir & "\*.xls")
While strExtension <> vbNullString
Set wbOpen = Workbooks.Open(MyDir & "\" & strExtension)
With wbOpen
Set rgFound = Range("B1").Find("draw", MatchCase:=False)
If rgFound Is Nothing Then
Range("B1").replace What:="prep", Replacement:="Tank prep"
Else
End If
Dim SaveName As String
SaveName = ActiveSheet.Range("B8").Text
ActiveWorkbook.SaveAs fileName:="C:\Processed data\ALIMS data\" & _
SaveName & ".txt"
.Close SaveChanges:=False
End With
strExtension = Dir
Wend
Application.ScreenUpdating = True
End Sub
