Excel IF statement

SEE BELOW CODE.

The snippet seen is being applied to an excel file (only provided to show how I'm accessing the file).  I have 2 columns I need to clean up.  The listed code will accomplish most of what I want, but I have 1 other issue:  I have a column in this sheet (column D) that contains location codes.  Location codes are typically numbers, except for 1 code:  005-1.  Based on how my database is set up and coded, I can't accept that value (hence my need to clean columns).  I need to change 005-1 to 005 (or just 5 will be fine).  I can't find the right syntax to express this.  Basically I want:

If ColumnD Value = "005-1" then change to "005" (or "5")

But understand, this will be run from a form created in VB using Visual Studio, so I think I will need syntax different then the standard Excel.  I'm sure this is a lot easier than I'm making it.  I would appreciate any help or hint on this.
xlObj = CreateObject("Excel.Application")
            xlObj.Visible = False
            xlBook = xlObj.Workbooks.Open(strFileName)
            xlSheet = xlBook.Worksheets("HOURLY")

            xlSheet.Range("J1", xlSheet.Range("J" & xlSheet.Rows.Count).End(-4162)).Offset(1, 0).Copy()
            xlSheet.Range("K2").PasteSpecial(-4163)
            xlSheet.Range("K1").Value = "Units"
            xlSheet.Columns(10).Delete()
            xlBook.Save()

Open in new window

fizzlefryAsked:
Who is Participating?
 
TommySzalapskiCommented:
Since you aren't using native VBA, it might help to know that xlPart is 2 and xlByRows is 1 so the whole thing should perhaps look like
xlSheet.Range("D:D").Replace "005-1", "005", 2, 1, 0
Since 0 is False or just use False at the end.
0
 
TommySzalapskiCommented:
You could just use
XlSheet.Range("D:D").Replace "005-1", "005", xlPart, xlByRows, False
0
 
TommySzalapskiCommented:
Oh, also in vbscript the default values do not work so you will need three more Falses at the end
xlSheet.Range("D:D").Replace "005-1", "005", 2, 1, 0, 0, 0, 0

I'm pretty sure that's right. I'd test it, but my iPad doesn't have Excel ;)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Peter KipropCommented:
try the attached code
0
 
Peter KipropCommented:
try the attached code
xlObj = CreateObject("Excel.Application")
            xlObj.Visible = False
            xlBook = xlObj.Workbooks.Open(strFileName)
            xlSheet = xlBook.Worksheets("HOURLY")

            xlSheet.Range("J1", xlSheet.Range("J" & xlSheet.Rows.Count).End(-4162)).Offset(1, 0).Copy()
            xlSheet.Range("K2").PasteSpecial (-4163)
            xlSheet.Range("K1").Value = "Units"
            xlSheet.Columns(10).Delete()
            Dim MyRange As Range, cell As Range
            Set MyRange = Range("D:D") '
            For Each cell In MyRange
                If cell.Value = "005-1" Then
                    cell.Value = "005"
                End If
            Next
            xlBook.Save()

Open in new window

0
 
Peter KipropCommented:
fizzlefry,

on the previously attached code remove the line , cell As Range
0
 
TommySzalapskiCommented:
Iterating over every cell with a for each will run noticeably slower than the replace function.

If you want it to only check the whole contents of the cell, use xlWhole instead of xlPart. (xlWhole has a value of 1).
0
 
fizzlefryAuthor Commented:
Tommy...  Thanks for the quick response.  The following code is working within VB:

xlSheet.Range("D:D").Replace("005-1", "005", 2, 1, 0)

However, if it doesn't find something matching 005-1, this code produces an error saying it can't find anything to replace, however it DOES continue through the rest of the code properly.  I was wondering if there was a way to attach an IF to this, just so it doesn't produce an error and confuse the user.  If we could get that logic correctly, than I will consider this a complete solution.

Thanks again!
0
 
TommySzalapskiCommented:


I would assume that wrapping
xlObj.DisplayAlerts = False

and

xlObj.DisplayAlerts = True

around the code should fix it.
0
 
fizzlefryAuthor Commented:
Thanks again for the help, Tommy.  Always appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.