shellecj
02-03-2013, 08:13 PM
Hi All,
I am still fairly new to VBA but please bear with me.
I have workbook with a list of properties. Each time we acquire a new property, we order an appraisal on it and as such column G gets filled out with the date the appraisal was ordered and the date it was completed gets entered in column H (these two columns are actually pulled from a certain field in our system). From that point on, a new appraisal is ordered on the property every 60 days but our system has a separate field for updated appraisals ordered/received and we reuse these two fields for all subsequent appraisals ordered….these two fields pull into columns K & L of a weekly report I pull. Each week, after pulling this report, I manually go through copy the Initial Appraisal Date Ordered and Received from column G & H into columns K & L but only if the cells in both column K AND L are blank (so that I can collapse the first appraisal ordered/Received columns to get them out of the way) and I have a formula set to look at column K and if the date is >=50 days to alert me that a new report is due. I need a macro to do this automatically.
After an exhaustive search on the internet, the closest solution I can find is the code below (my apologies, but don’t remember which site I found it on). This works great but it only fills out the date in column K (referencing the Date Ordered from Col G and copies it to Date Ordered in Col K) but I also want to copy the date the last Appraisal was completed as well (Col H) to be copied into Col L. Each week I pull my spreadsheet there are going to be many rows where the cells in column K & L are blank because it hasn't been that long since the initial appraisal was ordered (in columns G & H) so I want to copy the SET of dates from columns G & H into K & L.
Sub FindFillBlanks2()
Dim iCell As Range
For Each iCell In Range("K4:K400")
If iCell.Value = "" Then
iCell.Value = iCell.Offset(0, -4).Value
End If
Next iCell
End Sub
I've attached a copy of my workbook if it helps to reference it? Any assistance I can get would be greatly appreciated! :help
I am still fairly new to VBA but please bear with me.
I have workbook with a list of properties. Each time we acquire a new property, we order an appraisal on it and as such column G gets filled out with the date the appraisal was ordered and the date it was completed gets entered in column H (these two columns are actually pulled from a certain field in our system). From that point on, a new appraisal is ordered on the property every 60 days but our system has a separate field for updated appraisals ordered/received and we reuse these two fields for all subsequent appraisals ordered….these two fields pull into columns K & L of a weekly report I pull. Each week, after pulling this report, I manually go through copy the Initial Appraisal Date Ordered and Received from column G & H into columns K & L but only if the cells in both column K AND L are blank (so that I can collapse the first appraisal ordered/Received columns to get them out of the way) and I have a formula set to look at column K and if the date is >=50 days to alert me that a new report is due. I need a macro to do this automatically.
After an exhaustive search on the internet, the closest solution I can find is the code below (my apologies, but don’t remember which site I found it on). This works great but it only fills out the date in column K (referencing the Date Ordered from Col G and copies it to Date Ordered in Col K) but I also want to copy the date the last Appraisal was completed as well (Col H) to be copied into Col L. Each week I pull my spreadsheet there are going to be many rows where the cells in column K & L are blank because it hasn't been that long since the initial appraisal was ordered (in columns G & H) so I want to copy the SET of dates from columns G & H into K & L.
Sub FindFillBlanks2()
Dim iCell As Range
For Each iCell In Range("K4:K400")
If iCell.Value = "" Then
iCell.Value = iCell.Offset(0, -4).Value
End If
Next iCell
End Sub
I've attached a copy of my workbook if it helps to reference it? Any assistance I can get would be greatly appreciated! :help