VBA How to enter If Statements

[loribear180]

Hello all,

Excel newbie here ? I learned about formatting a spreadsheet with If Statements, however, the way I learned them is by inputting the formula into the each specific field.

A colleague shared a spreadsheet with macros, however, I'm trying to fix a certain one. Currently, if there is no input my field changes to '0'. How do I enter an If Statement into the actual code? I think I attached the code correctly below:

'Converts Formulas to values for on each tab
Sub ConvertFormulasToValuesAllWorksheets()

    Dim ws As Worksheet, rng As Range

    For Each ws In ActiveWorkbook.Worksheets

        For Each rng In ws.UsedRange

            If rng.HasFormula Then

                rng.Formula = rng.Value

            End If

        Next rng

    Next ws

End Sub

 

[Eric W]

Try changing this line:

rng.Formula = rng.Value

to

rng.Value = IIf(rng.Value=0,"",rng.Value)

 

[loribear180]

Try changing this line:

rng.Formula = rng.Value

to

rng.Value = IIf(rng.Value=0,"",rng.Value)

Hi Eric, unfortunately that didn't work. I think it's because I failed to explain I'm entering a phrase in the column, however, when the fields are blank, my spreadsheet has a bunch of 0s. I just want to fix it so if the column is blank so are the fields.

This spreadsheet has a lot of info I'm not too familiar with (in regards to the coding of it). It has an input tab that transcribes the info to 25 other tabs, and then exports all 25 tabs into their own spreadsheet.

 

[Eric W]

Are you just putting your phrase in row 1 of each column? What are the formulas in the columns that are being replaced? It sounds like they refer to the phrase?

 

[loribear180]

Are you just putting your phrase in row 1 of each column? What are the formulas in the columns that are being replaced? It sounds like they refer to the phrase?

Yes it's just a phrase in each column. No actual formulas, that is what I'm confused about. I'm not sure why the code reads "HasFormula". Shouldn't it be "IsText"?

 

[johnnyL]

@loribear180 If you are just trying to 'Delete' the zeros, you could try something like the following:

If rng.Value = 0 Then rng.Value = vbNullString

 

[loribear180]

@loribear180 If you are just trying to 'Delete' the zeros, you could try something like the following:

If rng.Value = 0 Then rng.Value = vbNullString

Hi Johnny! I tried that and I continue with the same issue. The problem must be elsewhere then I see 6 modules but none of the other ones appear to have any sort of code related to the values. I think at this point i'll just have to learn to recreate this spreadsheet and attempt to fix some quirks I saw.

 

[Marc L]

Hi,​

​

to avoid a NES link a sample workbook on a files host website like Dropbox for example …​