Solved

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

Posted on 2016-10-05
8
85 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

737 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