Null value Excel

[MikeC]

Re: Null value Excel

Thanks Stan,
I went ahead and added the multiple batches.....for the 3 loops (I only showed one) only two of them added any speed.....about a 15% increase overall. Looking at the code, I think that there is nothing more easily done to increase the speed than this in how I am doing it (using some of the other methods Ira has suggested could I know probably such as unrolling the loops--).
EDIT--unless there is a faster function than word() to obtain the Name in the list within the loop

Steve,
The problem with that method is that it will blank out ALL zeroes where I only wanted to blank out those that met certain criteria (the first column having a value). I appreciate the thought though.

[G Gabriel]

Re: Null value Excel

Without testing, an update operation or script would be faster or simply run a script that adds a field to the final table, the one generated after crosstab etc and add the values to that field based on the expression.
You might be able to do this during the crosstab without the need of additional fields.

[MikeC]

Re: Null value Excel

G,
I attempted to use an Update operation, but it hard codes the fields and the fields change (dependent upon which and how many companies are chosen) . I already have had to create a dynamic Crosstab and a dynamic Join operation, but the Update seemed, at the time, to be enough different that I decided to go the current route/method.

I do understand about adding the field and an expression and that it may very well be faster.....again, I chose what at the time was easiest for me and the speed is more than fast enough.

Although a bit of this thread is probably above most beginners, hopefully it will be of some use to others who want to create numeric null values....and also now how to increase script speed a bit. Maybe even how to create a simple loop.

It was threads such as this that had enabled me to learn a little about how to do such things. I still have the basic loop code snippets in my help file....and leave them there so I can use them as examples for the messageboard when needed.

[grayfox5]

Re: Null value Excel

Mike,

Don't know if you're going with the copy-paste option for the customer to use, but you could also incorporate an Excel macro in the spreadsheet. Looking at your sample, it appears that there's always a value in column C, whether it's a real number or a zero. I just tested this macro based on the sample from your image:

Sub RemoveZeros()
'
' RemoveZeros Macro
' Macro recorded 12/31/2009 by Jack Ross
'

'
Range("E2").Select

Do Until ActiveCell = ""
If ActiveCell.Value = 0 Then
If ActiveCell.Offset(0, -4).Value <> "" Then
Selection.ClearContents
End If
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

The macro starts by making E2 the active cell, then for every 0 it finds in column C, IF there's an entry in column A, it removes the zero, otherwise leaves it. Then it moves down one row and starts over, until there are no more entries in column C. You could attach the macro to a button in the Excel toolbar, then the customer could simply click it and have it run automatically.

Jack

[grayfox5]

Re: Null value Excel

Mike,

Whoops, sorry, I had put too many columns in my sample. This would be the correct macro:

Range("C2").Select

Do Until ActiveCell = ""
If ActiveCell.Value = 0 Then
If ActiveCell.Offset(0, -2).Value <> "" Then
Selection.ClearContents
End If
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

[G Gabriel]

Re: Null value Excel

Here is a simplified macro and a video showing the macro in action.
I didn't want to go the macro route thinking pushing a button might be too advanced for some users.
Other options include DDE or a 3D sheet automatic calculation.

Sub Zero()
' Zero Macro
' Macro recorded 12/31/2009 by Gabriel'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[-1]=0),"""",RC[-1])"
    Range("C1").Select
    Selection.Copy
    Range("B1:C1").Select
    Range("C1").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("C1:C3").Select
    ActiveSheet.Paste
'
End Sub

[Marcel Kollenaar]

Re: Null value Excel

Hi Mike,

A small variant on grayfox5:

Public Sub ClearCell()
Dim cell As Object
Columns(3).Select
For Each cell In Selection
    If cell.Value = "" Then Exit For
    If cell.Value = "0" Then cell.ClearContents
Next
End Sub

You can insert this code in the excel file in the same way as described in this post.

[grayfox5]

Re: Null value Excel

G,

Not to argue the point...since I haven't done any exporting yet to Excel from Alpha, I don't know if the file has to be created everytime (which I assume it would), in which case the macro button wouldn't be attached to the newly created Excel file. The only way around that would be to attach the macro permanently to the personal.xls file, which is opened every time Excel is, and have a button on the toolbar which would run that macro. As for making it simpler, you could condense my submitted version to:

Sub RemoveZeros()
Range("C2").Select

Do Until ActiveCell = ""
If ActiveCell.Value = 0 And ActiveCell.Offset(0, -2).Value <> "" Then Selection.ClearContents
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Demo attached.

[G Gabriel]

Re: Null value Excel

The file doesn't have to be created anew each time but I have no problem putting this macro or any other anywhere you wish. It's just for illustration purpose only.
The simplification I am referring to is mass copy instead of going down the cells which might take a little longer if you have 1000's of records but again Excel is pretty fast.

[grayfox5]

Re: Null value Excel

I have no problem putting this macro or any other anywhere you wish.

I'll try not to take that personally. ;-) Just kidding. Happy New Year!

[MikeC]

Re: Null value Excel

Thanks all,
What I have is working, but is always of benefit to see how to accomplish a goal with alternative methods...I have placed everything into my help file for future reference as am fairly certain I will be running across a time when it will be necessary to manipulate Excel in just such ways.