?
Solved

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

Posted on 2016-10-05
8
Medium Priority
?
102 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 27

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 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