Using VBA to set default value if cell is blank

I have a column of data (F7:F206) that if a cell within that range is blank then it should display a default value from a neighboring range (E7:E206)

Each range has a name assigned to it so how would I go about using the name ranges instead of the cell references?

F7:F206 = ActFTE
E7:E206 = RecFTE

Thank you,

Edwin
gixxer1020Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
You might try a macro like the following to set the default values of your named range ActFTE.
Sub Defaulter()
Dim cel As Range, rg1 As Range, rg2 As Range
Dim i As Long, n As Long
Application.ScreenUpdating = False
Set rg1 = ActiveWorkbook.Names("ActFTE").RefersToRange
Set rg2 = ActiveWorkbook.Names("RecFTE").RefersToRange
n = rg1.Cells.Count
For i = 1 To n
    If rg1.Cells(i) = "" Then rg1.Cells(i).Value = rg2.Cells(i).Value
Next
End Sub

Open in new window

0
Steven HarrisPresidentCommented:
Each range has a name assigned to it so how would I go about using the name ranges instead of the cell references?

Range("F7:F206")

is exactly the same as:

Range("ActFTE")

So, the following code will look at "ActFTE" and if a blank cell exists, it will be populated with the value from the corresponding cell out of Column E.

For this, I used Offset instead of referencing "RecFTE":

Sub Q_28265485()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    Range("ActFTE").Select
    For Each r In Selection
        On Error Resume Next
        If r.Value = "" Then
            r.Value = r.Offset(0, -1).Value
        End If
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window

0
gixxer1020Author Commented:
Thanks ThinkSpaceSolutions (TSS going forward)  =)

However I get an error message that "r:" is not defined.

Thanks,

Edwin
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Steven HarrisPresidentCommented:
Try using this:

Sub Q_28265485()
    Dim r As Range
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    Range("ActFTE").Select
    For Each r In Selection
        On Error Resume Next
        If r.Value = "" Then
            r.Value = r.Offset(0, -1).Value
        End If
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window


See the attached workbook.
Q-28265485.xlsm
0
gixxer1020Author Commented:
TSS,

Your code is watching the whole ActFTE range and changing everything in that range to the default in range RecFTE.  I'm looking to place this code in the WorkSheet Change Event so that it is triggered when I change a value in the ActFTE range but only for the cell I just edited.

Byundt,

It looks like your code may do what i'm looking for but when I place your code in the Worksheet Change Event it give me an error:

          application-defined or object-defined error

and the code stops at

          Set rg1 = ActiveWorkbook.Names("ActFTE").RefersToRange


Thanks,

Edwin
0
byundtMechanical EngineerCommented:
If you just want to put default values in ActFTE that were just changed by the user, try the following Worksheet_Change sub. It will work for both single cell changes as well as multiple non-contiguous cells.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, rg1 As Range, rg2 As Range
On Error Resume Next
Set rg1 = ActiveSheet.Names("ActFTE").RefersToRange                             'If ActFTE and RecFTE have local scope
Set rg2 = ActiveSheet.Names("RecFTE").RefersToRange
If rg1 Is Nothing Then Set rg1 = ThisWorkbook.Names("ActFTE").RefersToRange     'If ActFTE and RecFTE have global scope
If rg2 Is Nothing Then Set rg2 = ThisWorkbook.Names("RecFTE").RefersToRange
On Error GoTo 0
If rg1 Is Nothing Or rg2 Is Nothing Then Exit Sub

Set rg1 = Intersect(Target, rg1)
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cel In rg1.Cells
    If cel = "" Then cel.Value = Intersect(cel.EntireRow, rg2).Value
Next
Application.EnableEvents = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
Edwin,
If instead you want to update the default values in the entire ActFTE range when any value in that range is updated by the user, then consider the following:

Worksheet_Change event sub:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg1 As Range
On Error Resume Next
Set rg1 = ActiveSheet.Names("ActFTE").RefersToRange                             'If ActFTE has local scope
If rg1 Is Nothing Then Set rg1 = ThisWorkbook.Names("ActFTE").RefersToRange     'If ActFTE has global scope

If Not Intersect(rg1, Target) Is Nothing Then Defaulter
On Error GoTo 0
End Sub

Open in new window

I modified the Defaulter sub to use ThisWorkbook (rather than ActiveWorkbook), to turn events off while it runs, and to test both the worksheet and workbook names collection for your named ranges:
Sub Defaulter()
Dim rg1 As Range, rg2 As Range
Dim i As Long, n As Long
On Error Resume Next
Set rg1 = ActiveSheet.Names("ActFTE").RefersToRange                             'If ActFTE and RecFTE have local scope
Set rg2 = ActiveSheet.Names("RecFTE").RefersToRange
If rg1 Is Nothing Then Set rg1 = ThisWorkbook.Names("ActFTE").RefersToRange     'If ActFTE and RecFTE have global scope
If rg2 Is Nothing Then Set rg2 = ThisWorkbook.Names("RecFTE").RefersToRange
On Error GoTo 0
If rg1 Is Nothing Or rg2 Is Nothing Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
n = rg1.Cells.Count
For i = 1 To n
    If rg1.Cells(i) = "" Then rg1.Cells(i).Value = rg2.Cells(i).Value
Next
Application.EnableEvents = True
End Sub

Open in new window

To test the code, I created two dynamic named ranges: ActFTE and RecFTE. They used the following Refers To formulas:
=Sheet2!$E$7:INDEX(Sheet2!$E$7:$E$1000,MATCH("*",Sheet2!$E$7:$E$1000,1))
=OFFSET(ActFTE,0,1)
DefaultBlankCellsQ28265485.xlsm
0
gixxer1020Author Commented:
Thanks Byundt,

That first one you posted works great but I'll certainly keep the other for when needed.

Thanks,

Edwin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.