Default value in cell

[Bill Morris]

Hi

I have a spreadsheet whereby I want a formula ir VBA code to apply depending upon if another cell contains anything.

For example

A1 contain text. In B1 the user can add text, but if it is left blank then I want a dash ("-") as the default value.

If A2 doesn't contain anything then I want B2 to be left blank.

Thanks

 

[Joe4]

Are you running this "after the fact", or do you want it to run dynamically upon things being entered in A1?

 

[Bill Morris]

Are you running this "after the fact", or do you want it to run dynamically upon things being entered in A1?

Hi

I don't mind. As long as the empty cell contains the default character(s) when the doc is saved the it doesn't matter.

 

[Joe4]

How about something like this?

If you go into the VB Editor, and place the following code in the ThisWorkbook module, it will run automatically whenever you go to save your file. Note it is written to run against line 1 on Sheet3. Made adjustments as necessary.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


'   If cell A1 on Sheet3 is blank, make sure cell B1 is also blank...
    If Sheets("Sheet3").Range("A1") = "" Then
        Sheets("Sheet3").Range("B1") = ""
    Else
'   ... else if A1 is not blank, but B1 is, enter default into cell B1
        If Sheets("Sheet3").Range("B1") = "" Then Sheets("Sheet3").Range("B1") = "-"
    End If
    
End Sub

 

[Bill Morris]

How about something like this?

If you go into the VB Editor, and place the following code in the ThisWorkbook module, it will run automatically whenever you go to save your file. Note it is written to run against line 1 on Sheet3. Made adjustments as necessary.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


'   If cell A1 on Sheet3 is blank, make sure cell B1 is also blank...
    If Sheets("Sheet3").Range("A1") = "" Then
        Sheets("Sheet3").Range("B1") = ""
    Else
'   ... else if A1 is not blank, but B1 is, enter default into cell B1
        If Sheets("Sheet3").Range("B1") = "" Then Sheets("Sheet3").Range("B1") = "-"
    End If
    
End Sub

Thank you for your help....I'll give it a go!

 

[Bill Morris]

Thank you for your help....I'll give it a go!

Works a dream! Thanks!

If I wanted to run this check down to an unknown row number until cell "A?" has no value then how do I run this loop?

THanks for your help so far!

 

[Joe4]

This should do that:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


    Dim myRange As Range
    Dim cell As Range
        
'   Find last row in column A that is populated and set range
    Set myRange = Range("A1:" & Cells(Rows.Count, "A").End(xlUp).Address)


'   Loop through all cells in range
    For Each cell In myRange
    
'   If column A on Sheet3 is blank, make sure cell column B is also blank...
        If cell = "" Then
            cell.Offset(0, 1) = ""
        Else
'   ... else if column A is not blank, but column B is, enter default into column B
            If cell.Offset(0, 1) = "" Then cell.Offset(0, 1) = "-"
        End If
    Next cell
    
End Sub

 

[Bill Morris]

This should do that:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


    Dim myRange As Range
    Dim cell As Range
        
'   Find last row in column A that is populated and set range
    Set myRange = Range("A1:" & Cells(Rows.Count, "A").End(xlUp).Address)


'   Loop through all cells in range
    For Each cell In myRange
    
'   If column A on Sheet3 is blank, make sure cell column B is also blank...
        If cell = "" Then
            cell.Offset(0, 1) = ""
        Else
'   ... else if column A is not blank, but column B is, enter default into column B
            If cell.Offset(0, 1) = "" Then cell.Offset(0, 1) = "-"
        End If
    Next cell
    
End Sub

You, my friend, are a genius! I need to buy myself a VBA book and learn some of this stuff!

Thanks!

 

[Bill Morris]

You, my friend, are a genius! I need to buy myself a VBA book and learn some of this stuff!

Thanks!

How do I specify this for Sheet2 only which I renamed "Data"?

 

[mole999]

could be as simple as

Set myRange = sheets("Data").Range("A1:" & Cells(Rows.Count, "A").End(xlUp).Address)