Chewyhairball
Active Member
- Joined
- Nov 30, 2017
- Messages
- 312
- Office Version
- 365
- Platform
- Windows
Morning folks...well its morning here
I have this code that was kindly produced for me on here.
It does the following:
Replaces all the formulas in a row with the value when the text and also replaces any blank cells with a dash (-)
It does this whenever you manually enter the text 'complete' into a cell in the row.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified 6/27/18 5:40 AM EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastcolumn As Long
Dim ans As Long
Dim c As Range
ans = Target.Row
Lastcolumn = Cells(ans, Columns.Count).End(xlToLeft).Column
If Target.Value = "complete" Then
Rows(ans).Value = Rows(ans).Value
For Each c In Range(Cells(ans, 1), Cells(ans, Lastcolumn))
If c.Value = "" Then c.Value = "-"
Next
End If
End Sub
Its a great little code and have been thinking it might also be useful if it could be triggered if instead of the text 'complete' being entered manually but being produced from a formula...eg if(AB30=50,"complete","")
I thought it would be better to start a new thread for this as my last query was answered.
thanks
Rory
I have this code that was kindly produced for me on here.
It does the following:
Replaces all the formulas in a row with the value when the text and also replaces any blank cells with a dash (-)
It does this whenever you manually enter the text 'complete' into a cell in the row.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified 6/27/18 5:40 AM EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastcolumn As Long
Dim ans As Long
Dim c As Range
ans = Target.Row
Lastcolumn = Cells(ans, Columns.Count).End(xlToLeft).Column
If Target.Value = "complete" Then
Rows(ans).Value = Rows(ans).Value
For Each c In Range(Cells(ans, 1), Cells(ans, Lastcolumn))
If c.Value = "" Then c.Value = "-"
Next
End If
End Sub
Its a great little code and have been thinking it might also be useful if it could be triggered if instead of the text 'complete' being entered manually but being produced from a formula...eg if(AB30=50,"complete","")
I thought it would be better to start a new thread for this as my last query was answered.
thanks
Rory