Solved

If statement failing (delimited file)

Posted on 2014-03-11
4
154 Views
Last Modified: 2014-03-22
I have an if statement that is needed sometimes but not all the time.  It is after an extract that uses a delimited file, but sometimes it is already delimited and others it is not.  It depends on if the user in Excel changes the settings in the delimiter.  It appears that Excel remembers the last delimited settings and tries to apply that, even if it is not needed.

In todays example my if statement on cell D4 was not honored and it was clearly populated but it still tried to TextToColumn column A when it was not needed.  Is there another way that I can set the delimited settings so they are consistent, and then a different check, to see if anything right of column A, has a value?  If it comes in with values the delimited file came in with the previous settings that it remember from the last time it ran, but as soon as someone messes with the settings of Excels delimited, it the TextToColumn is needed again.

Any ideas how I can fix this?

   Sheets("COOIS-Done").Select
    If (D4 = "") Then 'if blank? do TextToColumns
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1)), TrailingMinusNumbers:=True
'if not continue
    End If

Open in new window

0
Comment
Question by:RWayneH
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39921016
RWayneH,

The if statement in your code is not even valid.

If (D4 = "") Then

Open in new window

is not in the correct format at all. You should not be able to run the macro at all.

It should be
If Range("D4") = "" Then

Open in new window


One other problem, are you sure D4 is ""? When you extract data from a CSV or TSV file, it's very often that a cells appears to be blank but in fact not. Is there any space sitting in the cell? Is there any invisible characters sitting in the cell?
0
 

Author Comment

by:RWayneH
ID: 39921102
Thanks, I will try the new version of the if statement.  I use the Other setting and the delimiter of |  It remembers this if  I do not touch the delimter or switch it.  As soon as I did chg it, that is when I need to run the TextToColumn.

The D4 is just a location that I threw out there.  What I need to know is what the delimiter settings are set to?  I have the extract in the clipboard and paste it into a workbook.

What I am looking for is something that can tell me, if I need to run the TextToColumn delimiter or not?  The only way I am thinking it will work is if I check anything right of column A? in which I just pick D4, or to have the macro set the delimited values prior to pasting in the data....  making sure all are unchecked, except the Other and have a value of "|"

I was  pretty sure that D4 was not empty, even a space = not empty..
How do I set the delimiter settings prior to paste? or write a different If that checks for values in B, C, E ....   Hope this makes sense.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39921215
If you do these regularly, you should know where does the delimiter character "|" first appear. (the first cell that contains | in your data extract)

For example A2.

You if statement can then be
   Sheets("COOIS-Done").Select
    If Range("A2") = "*|*" Then  'if cell A2 contains the character | do TextToColumns
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1)), TrailingMinusNumbers:=True
'if not continue
    End If

Open in new window

0
 

Author Closing Comment

by:RWayneH
ID: 39947772
EXCELlent!!  Thanks for the help -R-
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now