CODEWITHSUNDEEP
excelvba
Mikkel Astrup
Mikkel Astrup

Reputation: 415

Change excel cell text based on content

Im trying to make a VBA in excel that changes the content of a cell based on the text in the cells. All the cells I want to change contains a structure of "< (space) 0.005" the number however varies a lot and setting up all the different numbers would take too long. I have a base for an older project but I can't seem to tweak it to the required needs. 

Sub FillEmptyBlankCellWithValue()
Dim cell As Range
Dim InputValue As String
On Error Resume Next
InputValue = InputBox("Enter value that will fill empty cells in selection", _
"Fill Empty Cells")
For Each cell In Selection
If IsEmpty(cell) Then
cell.Value = InputValue
End If
Next
End Sub

Upvotes: 0

Views: 567

Answers (1)

Samuel Hulla
Samuel Hulla

Reputation: 7089

You're on a right track. But in general I'd avoid using Selection or any other Select operations as they are prone to many errors.

Instead, I'd recommend using active (detected dynamic) range, or let the user define the range. eg.

Option Explicit
Private Sub fill_blanks

Dim ws as Worksheet: Set ws = Sheets("Sheet1") ' set yours
Dim firstcell as String, lastcell as String, fillvalue as String
Dim datarange as Range

firstcell  = InputBox("Enter the starting cell of your data range")
lastcell = InputBox("Enter the last cell of your data range")
fillvalue = InputBox("Enter the value to fill blank cells with")

Set datarange = ws.Range(Trim(firstcell) & ":" & Trim(lastcell))

Dim cell as Range
For each cell in datarange
   If IsEmpty(cell) Then
        cell = fillvalue
   End If
Next cell

End Sub

You could also let them define the range inside a pre-defined cells in the worksheet, or detect the active range with .End(xlUp) for row and .End(xlToLeft) for column. Up to you really..

Note: This will paste the result as a String (or "Text") as a Cell Format, you also need to change the Range.Format property.

Upvotes: 1

Related Questions