Use if then statement when cell or row is empty

I thought I was finished with my project but ran into a snag when the data is missing. I have several scripts that run as macros in an excel spreadsheet. They are done in steps. Step 1 I take a bunch of .dat files in the same folder and create .xlsx files with the text to columns to separate the data into columns. I also add a couple of blank worksheets to each of the newly created excel workbooks. The .dat files contain about 7,000 rows of data and I only need 1 row that matches my criteria. Step 2, I extract the data that matches my criteria and put them in the new worksheets. I couldn't figure out how to do the search/extract with 2 values....so I search using 1 value and put in the first new worksheet, then I search that new worksheet and extract the one row using the last criteria/value. I may post that code to see if anyone can help me fine tune that. Anyway, in Step 3, the final step, I copy the row of data from the worksheets and insert into a new workbook. All of this works fine, except on occasion there will be no data. The monitor may be offline or whatever the reason, there will be no data for that time period.

Here's what the info will look like during my current runs. You will notice that there is no data and no row listed for 6:00

Example_1.png

I would like to insert an If Else statement so that if there is an empty row of data in the source worksheet then the text "missing data" will be inserted into the copy worksheet. So that the final workbook/worksheet will look like this.

Example_2.png


Here's my code. Note. I do try stuff I find on the internet and in this forum to try to help me solve my issue before I post my request for help. The people in this forum have been very helpful in getting me to my near completion of this project - it's for a non-profit, so your assistance is much appreciated. Thanks!

Please Login or Register  to view this content.

I'm not having any luck with the Call script. It stops at the first spreadsheet. I'll tinker with it some more. I appreciate your assistance. I don't need it to add or subtract the time. I just need that row to say "missing data" in cell A. That time column will actually get replaced later on with the correct time as this data is in GMT. So, I will have the offset time in another column. Thanks

Rollis13,

That worked! Thanks so much. I just had to think about it a bit. In your script, I did change the data start row to 2, for some reason it starts on row 2.

In my script I put the call function near the end, after the loop function. I had tried inserting it earlier and was running into issues.

Please Login or Register  to view this content.

Here's a screenshot of the finished excel. Thank you so much!


Example_3.png

Oops. I discovered an issue when you look at multiple days. The call script wants to insert missing data between the hours of 2300 and 0000 when there is no missing data.

Example_4.png

Thanks for all your help. rollis 13! I'll do some additional testing in various scenarios to see if all is well.

Just to keep it all straight. Here's the call subroutine as I currently have. I removed the Private just because it made it easier for me to edit during this testing period.

Please Login or Register  to view this content.

So close, but I did some additional testing and found that there are issues if the missing data is at 00:00 or at 23:00. It skips over those.

Example_6.png

I tried adding 2 additional ElseIf statements but said it needed to be an array

Please Login or Register  to view this content.

Here is a test spreadsheet with data. You will notice by the times that there are several lines of missing data. This is before our attempts to insert a row mentioning the "missing data".

I should add that this spreadsheet is created by taking sheet 3 from separate spreadsheets for every hour listed. So, Before this test spreadsheet result, there are several spreadsheets of data....this final spreadsheet combines the one row that is in sheet 3 of all those spreadsheets. Thus, in those missing data times, sheet 3 would be blank as there would be no data. I mention this...as that may be an option to insert a row of info. Then, when I would create this final spreadsheet with all the data, that missing data issue would have already been addressed.

Here's my latest attempt of fixing the missing data if that missing data happens at 0:00 or 23:00. This does not throw any errors. It still works for the missing data if not at 0:00 or 23:00. However, it does nothing....does not add the missing data row for missing data at 0:00 or 23:00. Maybe I am misunderstanding the x - and x + functions. I will experiment with that a little.

Please Login or Register  to view this content.

Customize the code below to suit your needs

Please Login or Register  to view this content.

Artik

Wow! That worked!

Both Artik and Rollis13 rule! Now, I need to go line by line of what Artik provided and see if I can make some sense out of why it works. LOL!

Thank you both!

I should be able to leave you alone for awhile, I hope. Anyway, I very much appreciate your time and assistance.