Two weeks ago I demonstrated how to transfer data from multiple excel sheets into a single repository. I have noticed one shortcoming with my previous Macro and that is for situations when the source file contains blank entries. In those situations the value for the next spreadsheet would get transferred to the blank cell. Here is an example. consider the input form below:
In the screenshot above, I have intentionally left the value in the “Customer ID” field blank. Here is the outcome of running the macro with four input files one of which contains a blank cell for Customer ID.
As can be observed in the screenshot above, one of the fields from file C (i.e. Customer ID) has been inputted in the line belonging to data fields from File B and one of the fields from file D has been inputted in the line belonging to data fields from file C.
In order to resolve this anomaly an IF statement similar to the one below needs be added to each block of code and for all of the fields:
If IsEmpty(textFile.Sheets("Registration Form").Range("D4").Value) = True Then
textFile.Sheets("Registration Form").Range("D4").Value = "N/A"
I have enclosed the full code below.
Here is the outcome after running the Macro using the revised code. As you could see the new output returns “N/A” whenever an empty value if detected within the five fields.