Solved

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

Posted on 2016-10-05
8
89 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 26

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 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