?
Solved

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

Posted on 2016-10-05
8
Medium Priority
?
94 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 46

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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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 …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

777 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