Prev: VBA code to protect cells Outside a given range
Next: Turn a range of cells into 'read only'
From: SEF on 27 Apr 2010 16:14
Thank you all - the macro was especially helpful!

"Gord Dibben" wrote:

> If linking formulas are like =Sheet1!A1 change them to
>
> =IF(Sheet1!A1="","",Sheet1!A1)
>
> Copy down and across.
>
> Or use a macro to change all existing formulas.
>
> Sub ZeroTrapAdd()
> Dim mystr As String
> Dim cel As Range
> For Each cel In ActiveSheet.UsedRange
> If cel.HasFormula = True Then
> If Not cel.Formula Like "=IF(*" Then
> mystr = Right(cel.Formula, Len(cel.Formula) - 1)
> cel.Value = "=IF(" & mystr & "="""",""""," & mystr & ")"
> End If
> End If
> Next
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 27 Apr 2010 10:53:01 -0700, SEF <SEF(a)discussions.microsoft.com>
> wrote:
>
> >I update a workbook weekly in separate worksheets. My master worksheet links
> >to the appropriate cells for the updated data. These are simple percentage
> >numbers (not forumlas, etc.) and only need to be a one-on-one link. The
> >problem I have is that if one of the cells is blank, it returns a 0 and I
> >need to to stay blank. Any ideas? There are about 57,000 cells I am working
> >with so I really don't want to have to update manually all blanks.
>
> .
>
From: Gord Dibben on 27 Apr 2010 17:26
Thanks for the feedback.

Gord

On Tue, 27 Apr 2010 13:14:01 -0700, SEF <SEF(a)discussions.microsoft.com>
wrote:

>Thank you all - the macro was especially helpful!
>
>"Gord Dibben" wrote:
>
>> If linking formulas are like =Sheet1!A1 change them to
>>
>> =IF(Sheet1!A1="","",Sheet1!A1)
>>
>> Copy down and across.
>>
>> Or use a macro to change all existing formulas.
>>
>> Sub ZeroTrapAdd()
>> Dim mystr As String
>> Dim cel As Range
>> For Each cel In ActiveSheet.UsedRange
>> If cel.HasFormula = True Then
>> If Not cel.Formula Like "=IF(*" Then
>> mystr = Right(cel.Formula, Len(cel.Formula) - 1)
>> cel.Value = "=IF(" & mystr & "="""",""""," & mystr & ")"
>> End If
>> End If
>> Next
>> End Sub
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 27 Apr 2010 10:53:01 -0700, SEF <SEF(a)discussions.microsoft.com>
>> wrote:
>>
>> >I update a workbook weekly in separate worksheets. My master worksheet links
>> >to the appropriate cells for the updated data. These are simple percentage
>> >numbers (not forumlas, etc.) and only need to be a one-on-one link. The
>> >problem I have is that if one of the cells is blank, it returns a 0 and I
>> >need to to stay blank. Any ideas? There are about 57,000 cells I am working
>> >with so I really don't want to have to update manually all blanks.
>>
>> .
>>

First  |  Prev  | 
Pages: 1 2
Prev: VBA code to protect cells Outside a given range
Next: Turn a range of cells into 'read only'