Solved

Excel VBA question, if cell is empty then replace it with specified value

Posted on 2016-10-05
8
73 Views
Last Modified: 2016-10-07
Excel Experts,

Please open attached file and you can that see that i have all the details, except i do not know how to do this with VBA.

any help is appreciated.
EE.xlsm
0
Comment
Question by:Flora
8 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41830361
"...for each target cell..."
In your sample workbook, you only have a single cell address listed in column E.  Will there ever be multi-cell ranges specified here?
0
 
LVL 6

Author Comment

by:Flora
ID: 41830723
Thanks Aikimark.

Yes, most of the time single cell but sometimes multiple cells like range("a1:b4")
If this is possible then it is super cool but if not possible then I can live with single cell method .
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 41831440
Try

Sub filltargetcells()
    Dim cel As Range, cel2 As Range, fname As String, wb As Workbook
    Dim chchk As Boolean
    For Each cel In ThisWorkbook.Sheets("Config").Range("A2:A" & Range("A2").End(xlDown).Row)
        fname = Dir(cel.Value & "\" & cel.Offset(, 1).Value & "." & cel.Offset(, 2).Value)
        Do While fname <> ""
            Set wb = Workbooks.Open(cel.Value & "\" & fname)
            Debug.Print wb.Name
            chchk = False
            For Each cel2 In wb.Sheets(cel.Offset(, 3).Value).Range(cel.Offset(, 4).Value)
                If IsEmpty(cel2) Then
                    cel2.Value = cel.Offset(, 5).Value
                    MsgBox "Change completed"
                Else
                    MsgBox "Skipped, data already existed" & vbCrLf & "Cell: " & cel.Offset(, 4)
                End If
            Next cel2
            wb.Close True
        fname = Dir()
        Loop
    Next cel
End Sub
1
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Author Comment

by:Flora
ID: 41833429
@Saqib

this is great!  i could never imagined this could be done with this short code. i was expecting multiple page VBA code.

thank you so much.

one small teak though. in stead of message boxes , can the value of message boxes be filled in the column G of "Config" sheet?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41833450
Not for points,

altering Saqib's code to this

Sub filltargetcells()
    Dim cel As Range, cel2 As Range, fname As String, wb As Workbook
    Dim chchk As Boolean
    For Each cel In ThisWorkbook.Sheets("Config").Range("A2:A" & Range("A2").End(xlDown).Row)
        fname = Dir(cel.Value & "\" & cel.Offset(, 1).Value & "." & cel.Offset(, 2).Value)
        Do While fname <> ""
            Set wb = Workbooks.Open(cel.Value & "\" & fname)
            Debug.Print wb.Name
            chchk = False
            For Each cel2 In wb.Sheets(cel.Offset(, 3).Value).Range(cel.Offset(, 4).Value)
                If IsEmpty(cel2) Then
                    cel2.Value = cel.Offset(, 5).Value
                    cel.Offset(, 6).Value = "Change Completed"
                    
                Else
                   cel.Offset(, 6).Value = "Skipped, data already existed in " & cel.Offset(, 4)
                End If
            Next cel2
            wb.Close True
        fname = Dir()
        Loop
    Next cel
End Sub

Open in new window

1
 
LVL 6

Author Closing Comment

by:Flora
ID: 41833458
Thanks a lot Saqib
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41833565
Assigning the msgbox messages to the cells is somewhat meaningless when the filename is *
1
 
LVL 6

Author Comment

by:Flora
ID: 41833640
thank you Saqib.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question