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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TommySzalapskiCommented:
You could just use
XlSheet.Range("D:D").Replace "005-1", "005", xlPart, xlByRows, False
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.