VBA code to ignore blank cells and return a value

[jfr198631]

Hi everyone,
First time poster here - I'm pretty rusty on my vba (haven't used it since high school to be honest!)

I have a workbook that contains an inspection sheet with a list of assets. This sheet contains all the data that I need to export to a cover sheet (called RCR).

I need the macro to skip blank rows and only return data if it matches the current date and within a certain time range. The time ranges are 9am - 12pm, 12pm-5pm, 5pm to midnight.

Can someone help me with this?

Thanks,

Jessica

 

[DushiPunda]

Unfortunately, I'm at work now so I can't access dropbox. You didn't answer question 1:
1. What are the exact conditions you want to be met prior to moving data from "Inspection" to "RCR"?

 

[jfr198631]

Sorry, I responded to this earlier but the post is still pending - not sure why.

Ok, I have two sheets in this workbook (dropdown list is irrelevant for now)

The Inspection sheet is the only document where data will be entered:

In Columns E:G (Row 7:443) are drop down lists.

In Cell E5:G5 is the time of the first inspection data and then below that (E6:G6) are the vehicle classes (not sure if I should format this differently to make it easier to use)

Cell B1 contains this formula: =TEXT(TODAY(), "DD MMMM YYYY")

Now, when a weather event occurs, the officer will enter data under the appropriate inspection time.


In cells D7: M7 (and onwards through each row that requires updating).

If the row (E7:M7 and so on) doesn't contain any data, then that road hasn't got any updates and doesn't need to be copied.

But if the current time is 9am or after, I want the cells with data (A7, B7 etc) & (E:G) (for the first inspection) to copy in to cell A24, A25 & C24:E24.
I want this to loop through until all the rows with data are copied over in to the RCR sheet.
The second inspection is 12pm (or after) and when Cell B3 updates to that time AND there is data in the second inspection (H:J), then data will display instead in the RCR sheet.
If there is no new data, continue to display the old data until new data is entered. I want this to loop through again until the third inspection time range (5pm onwards)

I am open to all suggestions here on how to make this more simple. I am trying to do this in my spare time for a work project, because I don't have enough time at work to actually achieve what it is I want. So far, your help has been invaluable!

So conditions to be met:
(time in B3 is within any of these time ranges (9:00am, 12pm, 5pm) --- how can I incorporate these specific ranges in my vba and also in my sheet so it is recognized.
contains data in any of the columns D:M (inspection) -- and continue loop

I hope I am making sense, let me know if I'm not and apologies in advance.

 

[DushiPunda]

Got it! Makes a lot more sense now. Like I said earlier, I'm at work now and don't have access to the document you provided...but when I get off work and find the time to have a look at this, I will certainly do so. I do have a few more questions -
1. I don't remember off the top of my head, but I recall there only being a few (3-4) rows on your RCR sheet...if there are more rows to copy than there are rows currently available on RCR, do you want to insert new rows?
2. Conversely, during the next run, if there are now fewer rows to copy than are currently visible on the sheet, do you want to remove blank rows?
3. Final question - how do you intend to run the macro? Will it be via a button that you press (I think this would be the most logical solution)?

Regarding #3, and why I think a button would be the most logical solution...you say "if there is no new data, continue to display old data until new data is entered"...If you have this run by a button that you click after the data is entered, then you don't have to check if there is new data.

Actually, that brings up another question lol. What if some of the entries have been updated, but not other entries? Do you want to display only the updated entries on RCR or do you want to display the old/non-updated entries along with the updated entries?

 

[jfr198631]

oh *phew*! I was worried I was just spouting a whole lot of nonsense haha!

Now I have been playing with the sheet again - I am struggling over how to best include time ranges in the sheet because when I insert it as a "header" it won't allow duplicate values.

Any thoughts?

Ok number of rows - yes, can we insert new rows as needed, and delete them if not required.
Definitely want to run it from a button.

OH and now that you have said that, would love to also include code that will generate a new Inspection sheet with cells D:M empty (for the next lot of inspections etc - a new day, a new sheet)
I've saved it again here:

https://www.dropbox.com/s/<wbr>az3jlbiaqg1mr5o/EMR%20Roads_<wbr>Assetic_Rev4.xlsm?dl=0

PS You're a legend for doing this!!!

 

[DushiPunda]

Okay, just having a gander at what you've got. I think the way you set up the times on "Inspection (2)" is probably the best and easiest way. I made some formatting changes that I think makes it a lot easier to read and follow. Medium borders are segregating the different sections. Every other row is highlighted light gray with a conditional formatting formula.

https://drive.google.com/file/d/0B8eLYugt7qKGUnYwbVktVDN5LXc/view

Moving on. Can we pretend it's after 9 AM and have you fill in some simulated data and what the results should look like on RCR?

When you say generate a new inspection sheet...do you want to keep the old one? Or do you just want to clear all of the entered data and start fresh on the same sheet? There are a few ways we can handle this, but it depends on what exactly you want to do.

One last thing, you forgot to Dim Inspection As Worksheet in the EMR_RCR macro.

 

[jfr198631]

Perfect!
Ok, so it's after 9, and Bell Street is Closed to LV, Open with Caution to 4WD, Open with caution to Trucks on RCR sheet.

I would like to keep the old one and generate a new sheet set up the same way.

I will do that now. Thank you so much!

 

[jfr198631]

Just tried to run it but I am getting a "can't execute code in break mode" error.

Not sure what I am doing wrong? I need to present the sheet this week - can you please help with this?

 

[DushiPunda]

I'm so sorry for the late reply! Been busy with work and whatnot. I'm not sure what the execute code in break mode error means, but I assume you've put a break in there and that's causing something to fail. There is still a lot to do with the macro as it is now.

So, here's what I need from you in order to help you out the fastest.

https://drive.google.com/open?id=0B8eLYugt7qKGVGFsNTZ0R3dnMTQ

1. Fill in pretend/simulated data on RCR and inspection as if it was after 09:00 AM. Save the document, upload it.
2. Repeat as if it was after 12:00 PM. Save the document as a different name, upload it.
3. Repeat as if it was after 5:00 PM. Save the document as a different name, upload it.

Send me all three links.

From what you give me there, I should be able to get a pretty good idea of what I need to come up with in the macro code.

Instead of creating new inspection sheets, I think it's going to be much easier to save the sheet as it's own workbook, then clear all of the data out of the original sheet and change the date.

Anyway, gotta run for now. But if you can get me those 3 documents, I should be able to help you out!

 

[jfr198631]

I'm so sorry I took ages to do this - I've been hibernating with the flu! Ok, I've uploaded each one of them (Inspection 1 is 9:00am and so on)

https://www.dropbox.com/s/z5if4vkhgr8mj45/EMR Roads_Assetic_Rev5_Inspection 1.xlsm?dl=0

https://www.dropbox.com/s/3ikel4i94o9dktu/EMR Roads_Assetic_Rev5_Inspection 2.xlsm?dl=0

https://www.dropbox.com/s/vnsz59p1l9oq4bh/EMR Roads_Assetic_Rev5_Inspection 3.xlsm?dl=0

I'm so sorry for the delay - hoping you can still help?

 

[jfr198631]

Sorry! I've re-uploaded those!!! Hope you had a nice weekend! I'm still battling on with the flu - blegh! In between moving house, and working full time, I need a holiday!!!