Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differentiate between "" and Empty in array elements.
I have text files holding comma separated values.
They are like this: "bla-bla",,,,"",,"" "",,,,bla-bla,, Now what I have to do is change the "" values to Empty values, so ,"", will become ,, This is easy, but I also want to know how many files had such an alteration done. This now proves very difficult, because I can't differentiate between ,, and ,"", I can't do the alterations directly in the text file as I have to check a few things, which are best done in an array. I put the text files in an array and then the following loop runs: For i = 1 To LR For c = 1 To 35 If arr(i, c) = "" Then arr(i, c) = Empty bDoneClean = True End If Next Next The array has to be declared as a variant. Because I can't differentiate bDoneClean becomes true even if the array element was already empty, so I can't count how many files were altered. I have tried numerous ways to differentiate this, but found nil that works. Thanks for any advice. RBS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differentiate between "" and Empty in array elements.
Had another go and found the answer.
This does the trick: For i = 1 To LR For c = 1 To 35 If arr(i, c) = "" Then If Not arr(i, c) = 0 Then arr(i, c) = Empty bDoneClean = True End If End If Next Next RBS "RB Smissaert" wrote in message ... I have text files holding comma separated values. They are like this: "bla-bla",,,,"",,"" "",,,,bla-bla,, Now what I have to do is change the "" values to Empty values, so ,"", will become ,, This is easy, but I also want to know how many files had such an alteration done. This now proves very difficult, because I can't differentiate between ,, and ,"", I can't do the alterations directly in the text file as I have to check a few things, which are best done in an array. I put the text files in an array and then the following loop runs: For i = 1 To LR For c = 1 To 35 If arr(i, c) = "" Then arr(i, c) = Empty bDoneClean = True End If Next Next The array has to be declared as a variant. Because I can't differentiate bDoneClean becomes true even if the array element was already empty, so I can't count how many files were altered. I have tried numerous ways to differentiate this, but found nil that works. Thanks for any advice. RBS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differentiate between "" and Empty in array elements.
For i = 1 To LR
For c = 1 To 35 If arr(i, c) = "" Then if Not isempty(arr(i,c)) then arr(i, c) = Empty bDoneClean = True End If Next Next to illustrate, vVal starts as uninitialized Variant: vVal = empty ? vVal = "" True ? isempty(vVal) True vVal = "" ? vVal = "" True ? isempty(vVal) False -- Regards, Tom Ogilvy RB Smissaert wrote in message ... I have text files holding comma separated values. They are like this: "bla-bla",,,,"",,"" "",,,,bla-bla,, Now what I have to do is change the "" values to Empty values, so ,"", will become ,, This is easy, but I also want to know how many files had such an alteration done. This now proves very difficult, because I can't differentiate between ,, and ,"", I can't do the alterations directly in the text file as I have to check a few things, which are best done in an array. I put the text files in an array and then the following loop runs: For i = 1 To LR For c = 1 To 35 If arr(i, c) = "" Then arr(i, c) = Empty bDoneClean = True End If Next Next The array has to be declared as a variant. Because I can't differentiate bDoneClean becomes true even if the array element was already empty, so I can't count how many files were altered. I have tried numerous ways to differentiate this, but found nil that works. Thanks for any advice. RBS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differentiate between "" and Empty in array elements.
Thanks.
I thought I had done that one and thought it didn't work, but it works as well indeed. It looks more logical than If Not arr(i, c) = 0 Then RBS "Tom Ogilvy" wrote in message ... For i = 1 To LR For c = 1 To 35 If arr(i, c) = "" Then if Not isempty(arr(i,c)) then arr(i, c) = Empty bDoneClean = True End If Next Next to illustrate, vVal starts as uninitialized Variant: vVal = empty ? vVal = "" True ? isempty(vVal) True vVal = "" ? vVal = "" True ? isempty(vVal) False -- Regards, Tom Ogilvy RB Smissaert wrote in message ... I have text files holding comma separated values. They are like this: "bla-bla",,,,"",,"" "",,,,bla-bla,, Now what I have to do is change the "" values to Empty values, so ,"", will become ,, This is easy, but I also want to know how many files had such an alteration done. This now proves very difficult, because I can't differentiate between ,, and ,"", I can't do the alterations directly in the text file as I have to check a few things, which are best done in an array. I put the text files in an array and then the following loop runs: For i = 1 To LR For c = 1 To 35 If arr(i, c) = "" Then arr(i, c) = Empty bDoneClean = True End If Next Next The array has to be declared as a variant. Because I can't differentiate bDoneClean becomes true even if the array element was already empty, so I can't count how many files were altered. I have tried numerous ways to differentiate this, but found nil that works. Thanks for any advice. RBS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I change color of "active" cell to differentiate from rest | Excel Discussion (Misc queries) | |||
Adding new elements to an existing "list" | Excel Worksheet Functions | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
Excel 2007 PivotTable "arbitrary shape is not allowed when its elements cross a reference dimension" | Excel Discussion (Misc queries) | |||
Excel: How to import multiple XML "Repeating child elements" same. | Excel Worksheet Functions |