Compare less than today date & Autofill Issue

0
votes

Need help with my code. I have two issues.

  1. 1st issue is I try to make my code to loop if fulfill empty cell and date at column 1 less than today date
    And .Cells(rw, 1) < Format(dt, "dd-mmm")
    but this < today date not able to work. The macro still continue even date is greater than today date

  2. 2nd issue is where i would like to autofill from column 5 to 9 from previous row. I keep encounter range error
    Set rng = .Range(.Cells(rw - 1, 5), .Cells(rw, 9)) Selection.AutoFill Destination:=rng, Type:=xlFillDefault
    and
    Selection.AutoFill Destination:=Range(.Cells(rw - 1, 5), .Cells(rw 1, 5)), Type:=xlFillDefault

Sub DataGrab()
Dim rw As Long, x As Range, rng As Range
Dim extwbk As Workbook, twb As Workbook

Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("D:\Users\Desktop\Report " & Format(Now, "DD-MMM-YYYY") & ".xls")
Set x = extwbk.Worksheets("Summary").Range("A4:AF100")

dt = Date

With twb.Sheets("Sheet1")
    For rw = 3 To .Cells(Rows.Count, 1).End(xlUp).Row
        If .Cells(rw, 2) = "" And .Cells(rw, 1) < Format(dt, "dd-mmm") Then
            .Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 23, False)
            .Cells(rw, 3) = Application.VLookup(.Cells(rw, 1).Value2, x, 29, False)
            .Cells(rw, 4) = Application.VLookup(.Cells(rw, 1).Value2, x, 31, False)
            Set rng = .Range(.Cells(rw - 1, 5), .Cells(rw, 9))
            Selection.AutoFill Destination:=rng, Type:=xlFillDefault
            'Selection.AutoFill Destination:=Range(.Cells(rw - 1, 5), .Cells(rw 1, 5)), Type:=xlFillDefault
        End If
    Next rw
End With
extwbk.Close savechanges:=False
End Sub
2
excelvba
Try changing of .Cells(rw, 1) < Format(dt, "dd-mmm") with .Cells(rw, 1) < CLng(dt) . Excel doesn't care about the date format. It keeps the date as Long... – FaneDuru
@DirkReichel stil encounter Autofill method of Range class failed – LeoEY
@FaneDuru .Cells(rw, 1) < CLng(dt) yes, this help. Thanks so much. – LeoEY
@LeoEY: Glad to help! I will transfer my comment to an answer... If you will tick the check box to the left side of the code, this will set it like accepted answer... – FaneDuru
@FaneDuru thanks so much. Yup mark ticks on the comment. Left 2nd issue to troubleshoot – LeoEY

2 Answers

0
votes

Try changing of .Cells(rw, 1) < Format(dt, "dd-mmm") with .Cells(rw, 1) < CLng(dt) . Excel doesn't care about the date format. It keeps and operates date as Long.

0
votes

(since I cannot comment, i'll do it through an answer)

first of all you should add Option Explicit at the beginning of your module. (this forces you to create variables before you give them a value)

next up, you have .Cells(rw, 2) = " " I assume you are trying to get the value of that cell?

if you add a MsgBox .Cells(rw, 2), in your code, you see that it will not return the value of that cell. thats why you need to use .Cells(rw, 2).Value

Also, we don't check an empty cell with " " but with if IsEmpty(.Cells(rw, 2).Value)

this should push you in the right direction :)