CODEWITHSUNDEEP
excelvba
user14934649
user14934649

Reputation:

VBA Formulas populating one line too far

I have this formula that I'm populating that is pretty simple in VBA. My only issue is this calculation actually goes one line too far, and in my next step I sort descending creating further compounding issues.

Is this code below flawed?

Sub Formz()
    
        With ThisWorkbook.Worksheets("Dinosaurs")
    
            .Cells(2, 9).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row).Formula = "=IF(A2="""","""",ABS(G2))"
            End With
            
 
            
              
End Sub

Upvotes: 1

Views: 70

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Defining a Range

A Quick Fix

Sub Formz()
    With ThisWorkbook.Worksheets("Dinosaurs")
        .Cells(2, 9).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 1).Formula = "=IF(A2="""","""",ABS(G2))"
    End With
End Sub

Why - 1?

  • Because your range doesn't start in the first row.

  • The question should rather be "Why + 1?".

    Because

    Range("A1:A5") = Range("A1:A5).Resize(1, 1)

    in contrast to

    Range("A1:A5") = Range("A1:A5").Offset(0, 0)

    where you will not be using the + 1.

Sub FormzWhy()
    
    Const FirstRow As Long = 2
    Const Col As Long = 9
    
    With ThisWorkbook.Worksheets("Dinosaurs")
        LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
        Dim rg As Range
        
        ' Your way:
        Set rg = .Cells(FirstRow, Col).Resize(LastRow - FirstRow + 1)
        Set rg = .Cells(2, 9).Resize(LastRow - 2 + 1)
        Set rg = .Cells(2, 9).Resize(LastRow - 1)
        ' Your attempt was missing the '- 1':
        Set rg = .Cells(2, 9).Resize(.Cells(.Rows.Count, 9).End(xlUp).Row - 1)
        
        ' Ben's way (see in the comments):
        Set rg = .Range(.Cells(FirstRow, Col), .Cells(LastRow, Col))
        Set rg = .Range(.Cells(2, 9), .Cells(.Rows.Count, 9).End(xlUp))
    
    End With
    
    rg.Formula = "=IF(A2="""","""",ABS(G2))"

End Sub
  • Note that if e.g. the first row were 4 then it would be - 4 + 1 = - 3.

Upvotes: 1

Related Questions