IF STATEMENT

[vjay883]

Hi All,

Wondering if someone can help me with what I'm trying to do here.
I need code to execute the following steps:

1. Find column name 'AS_DESCRIPTION' on sheet1 (will have a different sheet name each time being ran)
2. Search whole column and If there are any commas replace with a space and if there are any pipes replace with a space else continue executing code.
3. Then find column name 'AS_DESCRIPTION_LD'
4. Repeat of step 2.

Help Appreciated!

Cheers
V

[Bob Phillips]

Do it in Excel with the macro recorder on, you will get your code.

[vjay883]

Here's the code if anyone needs it:


Range("A1").Select
'move down one cell from range selected
ActiveCell.Offset(1, 0).Select
'select active row
ActiveCell.EntireRow.Select
'search for AS_DESCRIPTION
Cells.Find(What:="AS_DESCRIPTION", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'select active cell column
ActiveCell.EntireColumn.Select
'replace , with " "
Selection.Replace What:=",", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'replace | with " "
Selection.Replace What:="|", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'The below is a repeat of the above but selects different column called AS_DESCRIPTION_LD
Range("A1").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Select
Cells.Find(What:="AS_DESCRIPTION_LD", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.EntireColumn.Select
Selection.Replace What:=",", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="|", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

[Bob Phillips]

You have a small problem with that code should the label AS_DECRIPTION_LD come before AS_DESCRIPTION in row 2. Because you use xlPart in the find, it will land on the AS_DESCRIPTION_LD cell when looking for AS_DESCRIPTION, and then when it looks for and finds AS_DESCRIPTION_LD, the replacements will already have been done, and no replacements will be done on AS_DESCRIPTION. You can fix that by using xlWhole.

You can then tidy the code, removing all of the selects, like so


Rows(2).Find(What:="AS_DESCRIPTION", _
After:=ActiveCell, _
LookIn:=xlFormulas , _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

With ActiveCell.EntireColumn
.Replace What:=",", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="|", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With

'The below is a repeat of the above but selects different column called AS_DESCRIPTION_LD
Rows(2).Find(What:="AS_DESCRIPTION_LD", _
After:=ActiveCell, _
LookIn:=xlFormulas , _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

With ActiveCell.EntireColumn
.Replace What:=",", _
Replacement:=" ", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="|", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With


From this you can see that a lot of the code is repeated, so you can put the find replace in a subroutine and pass the label


Sub TidyUpData()

Call ReplaceCharacters("AS_DESCRIPTION")
Call ReplaceCharacters("AS_DESCRIPTION_LD")
End Sub

Private Sub ReplaceCharacters(ByVal Label As String)

Rows(2).Find(What:=Label, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

With ActiveCell.EntireColumn
.Replace What:=",", _
Replacement:=" ", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="|", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub

[vjay883]

You have a small problem with that code should the label AS_DECRIPTION_LD come before AS_DESCRIPTION in row 2. Because you use xlPart in the find, it will land on the AS_DESCRIPTION_LD cell when looking for AS_DESCRIPTION, and then when it looks for and finds AS_DESCRIPTION_LD, the replacements will already have been done, and no replacements will be done on AS_DESCRIPTION. You can fix that by using xlWhole.

You can then tidy the code, removing all of the selects, like so


Rows(2).Find(What:="AS_DESCRIPTION", _
After:=ActiveCell, _
LookIn:=xlFormulas , _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

With ActiveCell.EntireColumn
.Replace What:=",", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="|", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With

'The below is a repeat of the above but selects different column called AS_DESCRIPTION_LD
Rows(2).Find(What:="AS_DESCRIPTION_LD", _
After:=ActiveCell, _
LookIn:=xlFormulas , _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

With ActiveCell.EntireColumn
.Replace What:=",", _
Replacement:=" ", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="|", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With


From this you can see that a lot of the code is repeated, so you can put the find replace in a subroutine and pass the label


Sub TidyUpData()

Call ReplaceCharacters("AS_DESCRIPTION")
Call ReplaceCharacters("AS_DESCRIPTION_LD")
End Sub

Private Sub ReplaceCharacters(ByVal Label As String)

Rows(2).Find(What:=Label, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

With ActiveCell.EntireColumn
.Replace What:=",", _
Replacement:=" ", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="|", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub

Okay thanks, but 'AS_DESCRIPTION_LD' won't ever come before as description. But I appreciate you showing me about the sub routine version. I am a amateur at vba and can do just basic things with the recorder etc but like to learn more.

Thanks again appreicated
v

[Bob Phillips]

Never say never, it will catch you out :)

[SamT]

Never say never. xlPart used in sub Replace Characters

[Bob Phillips]

Never say never. xlPart used in sub Replace Characters

See, I told you! In actuality, the find should be xlWhole, both replace should be xlPart.