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]

What is the range of your data?

 

[jfr198631]

The sheet range is $A6:J465

 

[DushiPunda]

Okay,
And what columns contain the dates and the times?

 

[jfr198631]

The times are in Columns D, E, F and the date is in B1

 

[DushiPunda]

Okay,
So you want to go through A6:J465:
If the date in Column B is the current date and the time (from which column?) is within one of the time ranges then you want to copy what data to the other sheet? The entire row?
But, if the row is blank (will all cells within your range in a blank row be blank or only certain cells?), then you want to skip it and go to the next line?

I'm going to need to see your data, or a simulation of your data, and the expected results of the macro you're trying to create. But given what I've received from you so far, this is the basic premise:

Assuming:
1. "SheetName" is the name of the sheet that contains your data
2. "OtherSheet" is the name of the sheet you want to copy the data too

Dim intCounter as Integer
For i = 6 to 465    'Loop through the rows on "InspectionSheet"
    If Worksheets("InspectionSheet").Cells(i, "B").Value = Date Then 'If the value of B in the current row (i) = Date
        If TimeCheck Then    'This isn't what it should be because I don't know what specific conditions you want to meet for the time.
            intCounter = intCounter + 1 'Only count up if our conditions are met.
            Worksheets("RCR").Range("intCounter" is the next available row on "RCR").Value = Worksheets("InspectionSheet").Range.Value 'This line is not anywhere close to what it should be. But I don't know what data you want to copy.
        End If
    End If
Next i

 

[jfr198631]

I've saved it in a Dropbox: https://www.dropbox.com/s/obg7henh3b4za6p/EMR Roads_Assetic_Rev2.xlsm?dl=0

Also just realised my ranges are a bit out (sorry I was doing it from memory!)

So basically, if the cell meets the condition, I want the data to copy to the rcr. I need to copy over the asset name, I will add another column on the front page To include the asset class.
It will then reference the time and if it meets that condition, it will copy whether or not the road is open according to vehicle type.

Does that make sense? I will need to tweak the sheet a bit but if I have the basic concept I can rework the ranges if I have to.

 

[DushiPunda]

Sorry, I realized the "SheetName" and "OtherSheet" from my previous post was changed in the code I posted.

So, looking at this I see a few issues with the macro you have installed in module1 (EMR_RCR).

1. This line:

Dim Sheet1 As RCR, Sheet2 As Inspection, ThisWorkbook As EMRReport

Needs to be changed to:

Dim RCR As Worksheet, Inspection As Worksheet, EMRReport As Workbook

2. This:

Set EMRReport = Excel.ActiveWorkbook
Set RCR = myBook.Sheets("codes")
Set Inspection = myBook.Sheets("Sheet2")

Needs to be changed to:

Set EMRReport = ThisWorkbook 'Or ActiveWorkbook
Set RCR = Worksheets("RCR")
Set Inspection = Worksheets("Inspection")

Now:

1. What are the exact conditions you want to be met prior to moving data from "Inspection" to "RCR"?
2. What data from "Inspection" needs to be moved to "RCR"
3. Where on "RCR" is the data going?

3a. I see on RCR you have columns for ROAD, TYPE, Vehicle Class (Light/Utility, 4WD, Truck), and Comments...but none of those exist on the Inspection sheet.​

 

[jfr198631]

Thanks for all your help!
I've saved another version to here:

 

[jfr198631]

Sorry here:
https://www.dropbox.com/s/fgrzh5g15263ag8/EMR Roads_Assetic_Rev3.xlsm?dl=0

I'm not sure if this is the best way to format it.

I need to copy over asset name, asset class and then under the inspections, where it has different vehicle types (lv1, 4wd1, truck1) transfer over the data that comes from the drop down list (open, open with caution etc)

Finished RCR report should have the road name, its class (sealed or unsealed) and any data that comes under its vehicle class)

Thank you so much for all your help, I really appreciate it!