Macro to ignore cell value equal to zero in excel!

[codes]

I am working on a macro for an excel spreadsheet and i can't seems to find a way to make it go through a list of selected cells (that may contain numeric, alpha-numeric data) and ignore the cells where the value comes out to be equal to zero without defecting the format of this unprotected sheet. In the event that the cell is left blank the default value is equal to zero.

Your help is appreciated. Thanks!!!

 

[buxtongt]

I think we are going to need some more details around this. What are you trying to do with the cells? What do you want to happen with the ones that are 0 instead?

 

[kpark91]

Hi, if you want to go through a list of selected cells in your spreadsheet, you should declare your sub like this:

Sub yourSubName (ByVal Target As Range)
    'Your code goes here
End Sub

And then if you want to look through your selection, you can use For Each Loop.

Sub yourSubName (ByVal Target As Range)
    Dim c As Range
    For Each c In Target
        'Ignore values which comes out to be equal to zero
        If c.Value <> 0 Then
            'Your code
        End If
    Next c
End Sub

Lastly, if you want to not change any of the format, you have to store them first then restore it to a changed cell.
You will need to determine which formats you need to keep since it would be inefficient and cumbersome to store all the data about formatting for your cell.

For example, let's say you want to store cell highlight color.

Dim colorIndex As Integer
colorIndex = c.Interior.ColorIndex
c.Value = "changed value"
c.Interior.ColorIndex = colorIndex

Hope this helps.

 

[kpark91]

Double posted by mistake.

 

[codes]

Thanks for the reply...I have two sheets where the first one is an entry portal for data input and the second one that i set equal to sheet1 is where the data conversion actually takes place to be uploaded onto a system. Now the second sheet automatically contain zeros in all the cells because of the equality operator. The cells in sheet1 that are left blank have the default value of zero in sheet 2, for example a cell in sheet 2 that is formatted to decimal would have(0.00) by default if there is not any data entered in sheet1. Now it becomes a problem when i ran the macro because it returns all the cells (whether it contains actual data or zero). I would like the macro to not return anything for these cells that contain zeros.

 

[buxtongt]

Could you edit the macro that converts from sheet1 to sheet2 to leave the cell blank if there is nothing there in sheet1 instead of making it 0?

 

[codes]

That is also an alternate route that i am looking into but i am having difficulty with the code!

 

[codes]

@ kpark91:

If c.Value <> 0 Then... i would like it to set that cell equal to Blank but then that erases the format because the sheet cannot be protected. any ideas???​

 

[codes]

Alternately if i could have the macro move the cursor by one position in all the blank cells in sheet1 that would get rid of all the zeros in sheet2!

 

[kpark91]

@ kpark91:

If c.Value <> 0 Then... i would like it to set that cell equal to Blank but then that erases the format because the sheet cannot be protected. any ideas???​

Hi, like I've mentioned before, I don't know any way of storing all the formatting properties of a cell so you will need to do some research on how to store some of the properties you want.
Like I've shown in the example above for storing & restoring cell highlight color.

Moreover, if you want to make 0 a blank. you can use

If c.Value = 0 Then
    c.Value = ""
End If