niceguy21
10-02-2012, 08:36 AM
Hello VBA People,
I am very new to VBA and have been tasked with a solution to a problem on an Excel dashboard we are attempting to build.
I believe our best bet is an Auto-Macro, and what a powerful tool these would appear to be!
Problem:
In a simple columnar range A1:A1000, we need to insure NO empty cells can be entered: any empty cell is filled with "BLANK".
We require an Auto-Macro that will detect cells in the range have been entered and it then fills the empty ones.
This is as far as my working got:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Sheets("raw data").Range("A1:A1000")) Is Nothing Then
For Each cell In Target
If cell.Value <> Empty Or cell.Value = 0 Then
cell.value="blank"
End If
Next cell
End If
End Sub
definately and most certainly wrong! but maybe it needs a few corrections?
Best
I am very new to VBA and have been tasked with a solution to a problem on an Excel dashboard we are attempting to build.
I believe our best bet is an Auto-Macro, and what a powerful tool these would appear to be!
Problem:
In a simple columnar range A1:A1000, we need to insure NO empty cells can be entered: any empty cell is filled with "BLANK".
We require an Auto-Macro that will detect cells in the range have been entered and it then fills the empty ones.
This is as far as my working got:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Sheets("raw data").Range("A1:A1000")) Is Nothing Then
For Each cell In Target
If cell.Value <> Empty Or cell.Value = 0 Then
cell.value="blank"
End If
Next cell
End If
End Sub
definately and most certainly wrong! but maybe it needs a few corrections?
Best