IF STATEMENT

**vjay883** · 09-08-2014, 04:18 AM

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** · 09-08-2014, 05:09 AM

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

**vjay883** · 09-08-2014, 08:40 AM

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** · 09-08-2014, 01:29 PM

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** · 09-08-2014, 01:42 PM

Originally Posted by xld

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** · 09-08-2014, 03:06 PM

Never say never, it will catch you out

**SamT** · 09-08-2014, 03:29 PM

Never say never. xlPart used in sub Replace Characters

**Bob Phillips** · 09-09-2014, 01:44 AM

Originally Posted by SamT

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.

Thread: IF STATEMENT

Thread Tools

Display

IF STATEMENT

Posting Permissions