PDA

View Full Version : Solved: Need help with a Macro to copy cells in column G & H if cells in col K & L are blank



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

fredlo2010
02-03-2013, 09:30 PM
Just re size your range

Sub FindFillBlanks2()
Dim iCell As Range
For Each iCell In Range("K4:K400")
If iCell.Value = "" Then
iCell.Resize(1, 2).Value = iCell.Resize(1, 2).Offset(0, -4).Value
End If
Next iCell
End Sub

werafa
02-03-2013, 09:37 PM
Here is a formula to calculate the number of weeks overdue an 'appointment' is , with 99 being a 'no prior date'.

=IF(NOW()-MAX(I3:M3)>693,99,(NOW()-MAX(I3:M3)-70)/7)

you could implement this in VBA if you wish, or enter it as a new column

once you have this, you can use an 'if then' to test your condition.
the simplest way is to do something like"

for myRow = 1 to usedrange.rows.count
if range("C"& myrow).value > [60 days] then
range("D" & myrow).value = range("E" & myrow).value
end if
next myrow

shellecj
02-04-2013, 06:05 PM
Fredlo2010...thank you so much! This worked great! I appreciate the quick response!

Werafa...thanks, that's a neat tip! I think I'll save and hold onto that as well! It might come in handy one of these days!

Thanks again all! :bow: