Forum Discussion

clh14_96's avatar
clh14_96
Copper Contributor
Mar 22, 2024

VBA formula help

Hi all

 

Is someone please able to check the Syntax of this macro. For some reason, it does everything but add the formula in C8 and pull it down to the last cell and I can't figure out why. It just seems to skip over it which is why I think that my syntax may be wrong

 

Sub Load_File()
Dim LoadWS As Worksheet
Dim DataWS As Worksheet
Dim ProjectField As Range
Dim rw As Long, Cell As Range
Dim lr As Long
Dim LastRow As Long, i As Long
Application.ScreenUpdating = False
Set LoadWS = Worksheets("Load File")
LoadWS.Cells.ClearContents
Workbooks("Wkb1.xlsm").Sheets("Paste into here").Activate
Range("A1:ZA6").Select
Selection.Copy
Workbooks("Wkb1.xlsm").Sheets("Load File").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Set DataWS = Worksheets("Paste into here")
Set ProjectField = DataWS.Range("D7", DataWS.Range("D7").End(xlDown))
ProjectField.AutoFilter Field:=1, Criteria1:="50", Operator:=xlOr, Criteria2:="=100"
ProjectField.EntireRow.Copy Destination:=LoadWS.Range("A7")
ProjectField.AutoFilter
Columns("M:N").Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("Load File").Range("C:C").EntireColumn.Insert
With Worksheets("Load File").Select
Dim Formulas(1)
Formulas(1) = "=IF(IFERROR(MATCH(A8,'ENG Employee List'!B:B,0)>0,FALSE),""Authorised Resource"",""Unauthorised Resource"")"
Worksheets("Load File").Range("C8").Formula = Formulas
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Worksheets("Load File").Range("C8:C" & LastRow).FillDown
Application.DisplayAlerts = True
End With

End Sub

 

Thanks

Charlotte

  • clh14_96 

    You clear the entire worksheet Load File.

    So when you cut the (now empty) columns MM:N and insert a column into column A, the entire column A will be empty. That makes the formula pretty useless, if I interpret this correctly.

    Apart from that, try this:

    Below the line

     

    LastRow = ...

     

    Insert a new line

     

    MsgBox LastRow

     

    Run the macro. What is the value of LastRow?

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor

    clh14_96 

    The worksheet name "Load File" might have been misleading to Hans. It appears to be a data staging area, so initially clearing it is appropriate.


    Like Hans, I wonder what the value of your LastRow variable would be. I see two likely problems in the code:

    1. The Formulas array (of Variants, as you did not specify a data type) does not specify a lower bound. That's typical coding, but then in the absence of an explicit lower bound*, someone examining the code has to check for an Option Base statement at the top of the code module. If the array's lower bound is 0, your assignment to the Formula property will store the zeroth entry. But without a prior assignment to the zeroth entry, it contains Empty (the default value for uninitialized Variants).
    2. In my testing of the FillDown method, I noticed that if the range is a single cell, the formula that was stored there is erased. With your code, if LastRow is 8, the range is C8:C8, a single cell.


    * An explicit lower bound would be writing a declaration such as "Dim Formulas(0 To 1)" or "Dim YearsOfData(1950 To 2022)", with or without the As clause.


    So consider using this code in place of your With block:

        '----   Insert a column after those two.  From row 8 thru the last row,
        '       store a formula into column C that derives […?? something].
        With LoadWS
            .Range("C:C").EntireColumn.Insert
            '  --
            Dim Formulas(1) '<<< If you do not have an "Option Base 1" _
                    statement at the top of the module, that is equivalent to _
                    "Dim Formulas(0 To 1)".
            Formulas(1) = "=IF(IFERROR(MATCH(A8,'ENG Employee List'!B:B,0)>0,FALSE)" _
                    & ",""Authorised Resource"",""Unauthorised Resource"")"
            .Range("C8").Formula = Formulas 'That is apparently putting Formulas(0) _
                    into the cell's property; and as it contains Empty, the intended _
                    formula is not stored.  It's better to be more specific:
            .Range("C8").Formula = Formulas(1)
            LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("C8:C" & LastRow).FillDown
            '   Note: Snowman55 found that if LastRow _was_ 8, that action removed the
            '       cell's formula.  So consider wrapping the assignment inside an If.
        End With
        '----   [The following is unrelated code, so it's better to keep it outside the With block.]
        Application.DisplayAlerts = True


    The following notes are of not relevant to the missing formula problem, but speak to good coding:


    1. For ease of understanding, include comments in your code.


    2. You do not need to activate a worksheet and select a range from it to copy its data or paste data into it. For example:

        '----   Copy cells from some specific source.
        'Workbooks("Wkb1.xlsm").Sheets("Paste into here").Activate
        'Range("A1:ZA6").Select
        'Selection.Copy
        Workbooks("Wkb1.xlsm").Sheets("Paste into here").Range("A1:ZA6").Copy

    And using an unqualified Range object is somewhat dangerous, as it refers to ActiveSheet, which a user could change while the procedure is executing. (FYI, you can include a "LoadWS.Activate" in later code, as needed.  I'm guessing that the "Load File" worksheet is present only in Wkb1.xlsm, which is the workbook containing this procedure. Right?)


    3. The point of using a With block is to reduce the repetition of syntax (and possibly to improve performance). What follows "With" should be an object (typically; there is an exception) whose properties and/or methods you are going to reference within the block. "Worksheet("Load File").Select is not an object that exposes properties/methods. (I'm surprised the compiler does not flag that as an error.) That's why I changed your With block to start as:

    With LoadWS
        .Range("…

    which in this case is equivalent to:

    With Worksheets("Load File")
        .Range("…

     

    4. Your executable code starts with "Application.ScreenUpdating = False". While this procedure might be part of a series that is executed together, and the last in that series restores the setting, the absence of "Application.ScreenUpdating = True" at the bottom of this procedure might be an oversight.

     

Resources