Default value in cell depending on other cell value

[Bill Morris]

Hi

I have posted something similar to this before.

I have a spreadsheet whereby I want a formula or <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code to apply depending upon if another cell contains anything.

For example

On sheet "Data", Range "C38nwards" may contain text. In N38: the user can add text, but if it is left blank then I want a dash ("-") as the default value. This applies to C39 (N39), C40 (N40).....etc

If any of the cells in the C38: range are blank then I want the corresponding (offset (0, 11) cell in the N column to be left blank.

I need the code to check column "C" from cell C38 until it reaches a blank cell.




Thanks

 

[DILIPandey]

Hi Bill

If any of the cells in the C38: range are blank then I want the corresponding (offset (0, 11) cell in the N column to be left blank.

left blank OR you need dash "-" in this case ??



Regards,
DILIPandey

 

[DILIPandey]

Anyways, try below code:-

Sub DILIPandey()
'checking blanks in column C and
'populating blank in column n
For Each cell In Range(Range("c38"), Range("c" & Rows.Count).End(xlUp))
    cell.Select
    If ActiveCell.Value = "" Then
        ActiveCell.Offset(0, 11).ClearContents
    End If
Next
End Sub

Regards,
DILIPandey

 

[francoisdublin]

If I understand your question correctly this code would fill columns N and following with dashes if the matching column was left blank

Sub put_a_dash()
Dim rCell As Range


For Each rCell In Range("C38:M78") 'change the range as needed


If rCell.Value = "" Then
If rCell.Offset(0, 11).Value = "" Then rCell.Offset(0, 11).Value = "-"
End If




Next
End Sub

 

[Bill Morris]

Anyways, try below code:-

Sub DILIPandey()
'checking blanks in column C and
'populating blank in column n
For Each cell In Range(Range("c38"), Range("c" & Rows.Count).End(xlUp))
    cell.Select
    If ActiveCell.Value = "" Then
        ActiveCell.Offset(0, 11).ClearContents
    End If
Next
End Sub

Regards,
DILIPandey

Thank you for your response. Not sure if this will work.

I'm looking for code which will check down column C until it reaches a black cell, starting at C38.
Where cells in this range are not blank I require the corresponding cell in column N to contain either the default "-" or the user can input text in this cell.

 

[Bill Morris]

Hi

Any cell in the range C38: which is blank needs the corresponding cell in column N to be blanks also.

The list in column C ends with a blank cell, There is never the case where there is content in, say, cells C38-C50, nothing in C51, then text again in C52.....

 

[Bill Morris]

If I understand your question correctly this code would fill columns N and following with dashes if the matching column was left blank

Thank you for your response. Not sure if this will work.

I'm looking for code which will check down column C until it reaches a black cell, starting at C38.
Where cells in this range are not blank I require the corresponding cell in column N to contain either the default "-" or the user can input text in this cell.

Any cell in the range C38: which is blank needs the corresponding cell in column N to be blanks also.

The list in column C ends with a blank cell, There is never the case where there is content in, say, cells C38-C50, nothing in C51, then text again in C52.....

 

[DILIPandey]

Okay for default "-", use below code with slight modification:-

Sub DILIPandey()
'checking blanks in column C and
'populating blank in column n
For Each cell In Range(Range("c38"), Range("c" & Rows.Count).End(xlUp))
    cell.Select
    If ActiveCell.Value = "" Then
        ActiveCell.Offset(0, 11).Value = "-"
    End If
Next
End Sub

Regards,
DILIPandey

 

[Bill Morris]

Hi

I'm sorry that doesn't work.

I had this code but for some reason it populates cells N1 onwards with a "-" if there is something in C1 onwards. In this case the worksheet I'm using is called "Data"

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




Dim myRange As Range
Dim cell As Range

' Find last row in column C that is populated and set range
Set myRange = Sheets("Data").Range("C38:" & Cells(Rows.Count, "C").End(xlUp).Address)


' Loop through all cells in range
For Each cell In myRange

' If column C on Data is blank, make sure cell column N is also blank...
If cell = "" Then
cell.Offset(0, 11) = ""

' ... else if column C is not blank, but column N is, enter default into column N
ElseIf cell.Offset(0, 11) = "" Then
cell.Offset(0, 11) = "-"

End If

Next

End Sub

 

[DILIPandey]

Okay this means that you do not have data in c38 and onwards.

Put data in c38 and below - leave some blanks and then use your macor - should work.


Regards,
DILIPandey