Replace all null values with "null" 2

[UCAtech]

Hey everyone, I'm a PHP developer so this Microsoft stuff is throwing me for a loop!.

I have the need to populate all null values within the scope of the columns on all worksheets with "null".

Is this possible with VBA?

Dummy Data (What I have):
A B C D
1 Column1 | Column 2 | Column3 |
2 55342 | | 34223 |
3 343223 | 342343 | |
4 | 3243555 | 345555 |
5
6

Desired Data:
A B C D
1 Column1 | Column 2 | Column3 |
2 55342 | "null" | 23422 |
3 343223 | 342343 | "null" |
4 "null" | 3243555 | 345555 |
5
6

The replacing of null values should end at C and 5 (both of which will be changing daily.

Ben Boyd
Technical Writer/Programer/Student
opspeedydude@hotmail.com
Linuxbox Server Stats: 700mhz, 1024 MB RAM, 120 GB HD, Fedora 3
Laptop Stats: 2.0 Ghz, 1024MB RAM, 40GB HD, Windows XP Professional
Desktop Stats: 2.6 Ghz, 2048MB RAM, 3 x 120 GB HD, ATI Radeon x800 Pro, Windows XP Media Center Edition

 

[SkipVought]

Hi,

dim r as range
with activesheet.[A1].currentregion
  for each r in range([A2], .cells(.rows.count, .columns.count))
    with r
       if .value = "" then .value = "null"
    end with
  next
end with

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[UCAtech]

thanks it worked great after a few modifications

Ben Boyd
Technical Writer/Programer/Student
opspeedydude@hotmail.com
Linuxbox Server Stats: 700mhz, 1024 MB RAM, 120 GB HD, Fedora 3
Laptop Stats: 2.0 Ghz, 1024MB RAM, 40GB HD, Windows XP Professional
Desktop Stats: 2.6 Ghz, 2048MB RAM, 3 x 120 GB HD, ATI Radeon x800 Pro, Windows XP Media Center Edition

 

[UCAtech]

How would I do this same thing over MULTIPLE sheets in a workbook?

Ben Boyd
Technical Writer/Programer/Student
opspeedydude@hotmail.com
Linuxbox Server Stats: 700mhz, 1024 MB RAM, 120 GB HD, Fedora 3
Laptop Stats: 2.0 Ghz, 1024MB RAM, 40GB HD, Windows XP Professional
Desktop Stats: 2.6 Ghz, 2048MB RAM, 3 x 120 GB HD, ATI Radeon x800 Pro, Windows XP Media Center Edition

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[UCAtech]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim r As Range
Dim variableType As String
Dim awb As Workbook
Dim BackupFileName As String
Dim i As Integer

Set awb = ActiveWorkbook
BackupFileName = awb.FullName
i = 0
While InStr(i + 1, BackupFileName, ".") > 0
    i = InStr(i + 1, BackupFileName, ".")
Wend
If i > 0 Then BackupFileName = Left(BackupFileName, i - 1)
    BackupFileName = BackupFileName & ".xml"
    
    awb.SaveAs Filename:= _
        BackupFileName , _
        FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:= _
        False
 

'Empty = 0  Null = 1  Integer = 2  Long = 3  Single = 4  Double = 5  Currency = 6
'Date = 7  String = 8  Object = 9  Error = 10  Boolean = 11  Variant = 12
'Data Object 13  Decimal = 14  Byte = 17  Array = 8192

With ActiveSheet.[A1].CurrentRegion
  For Each r In Range([A2], .Cells(.Rows.Count, .Columns.Count))
    With r
        variableType = varType(.Value)
        If variableType = 0 Or variableType = 1 Then
            If .Value = "" Or .Value = Empty Then .Value = "0"
        ElseIf variableType = 2 Or variableType = 3 Or variableType = 4 _
        Or variableType = 5 Or variableType = 6 Or variableType = 14 _
        Or r.NumberFormat = "0.00" Or r.NumberFormat = "$#,##0.00" Or r.NumberFormat = "0" Then
            If .Value = "" Or .Value = Empty Then .Value = "0.00"
        ElseIf variableType = 8 Or variableType = 13 Or r.NumberFormat = "General" Then
            If .Value = "" Then .Value = "null"
         ElseIf r.NumberFormat = "h:mm;@" Then
            If .Value = "" Or .Value = Empty Then .Value = "0:00"
        ElseIf variableType = 7 Or r.NumberFormat = "m/d/yyyy" Then
            If .Value = "" Or .Value = Empty Then .Value = "1/0/1900"
        End If
    End With
  Next
   
End With

With awb
Application.StatusBar = "Saving this workbook..."
.Save
End With

End Sub

Ben Boyd
Technical Writer/Programer/Student
opspeedydude@hotmail.com
Linuxbox Server Stats: 700mhz, 1024 MB RAM, 120 GB HD, Fedora 3
Laptop Stats: 2.0 Ghz, 1024MB RAM, 40GB HD, Windows XP Professional
Desktop Stats: 2.6 Ghz, 2048MB RAM, 3 x 120 GB HD, ATI Radeon x800 Pro, Windows XP Media Center Edition

 

[PHV]

A starting point:
[!]Dim ws As Worksheet
For Each ws In awb.Worksheets[/!]
With [!]ws[/!].[A1].CurrentRegion
For Each r In Range([!]ws.[/!][A2], .Cells(.Rows.Count, .Columns.Count))
With r
variableType = varType(.Value)
If variableType = 0 Or variableType = 1 Then
If .Value = "" Or .Value = Empty Then .Value = "0"
ElseIf variableType = 2 Or variableType = 3 Or variableType = 4 _
Or variableType = 5 Or variableType = 6 Or variableType = 14 _
Or r.NumberFormat = "0.00" Or r.NumberFormat = "$#,##0.00" Or r.NumberFormat = "0" Then
If .Value = "" Or .Value = Empty Then .Value = "0.00"
ElseIf variableType = 8 Or variableType = 13 Or r.NumberFormat = "General" Then
If .Value = "" Then .Value = "null"
ElseIf r.NumberFormat = "h:mm;@" Then
If .Value = "" Or .Value = Empty Then .Value = "0:00"
ElseIf variableType = 7 Or r.NumberFormat = "m/d/yyyy" Then
If .Value = "" Or .Value = Empty Then .Value = "1/0/1900"
End If
End With
Next
End With
[!]Next[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[UCAtech]

thanks, worked great!

Ben Boyd
Technical Writer/Programer/Student
opspeedydude@hotmail.com
Linuxbox Server Stats: 700mhz, 1024 MB RAM, 120 GB HD, Fedora 3
Laptop Stats: 2.0 Ghz, 1024MB RAM, 40GB HD, Windows XP Professional
Desktop Stats: 2.6 Ghz, 2048MB RAM, 3 x 120 GB HD, ATI Radeon x800 Pro, Windows XP Media Center Edition