CODEWITHSUNDEEP
vbaexcel
BZ080
BZ080

Reputation: 11

VBA to iterate until last filled row

I got the below script from someone and then modified it to work with my workbook. Essentially what is happening is that the GoalSeek functionality is applied to Columns M and E.

So I am setting Column M to 0.3 and then calculating the corresponding values in Column E. At the moment, the first row of data starts in row 7 and ends in row 33. However, I use this to calculate financial deals and sometimes I have more deals than can fit in rows 7 to row 33 e.g. I might have rows from 7 to 60.

How can I modify the VBA code so it applies the GoalSeek until the very last row of data (so not the last row of the sheet which would be empty)?

This would avoid the need for me to manually edit the VBA each time my deal analysis extends beyond row 33. How can Excel 'recognise' that my data ends in row 60 for instance and automatically incorporate this into the VBA? 

Public Sub GoalSeeker()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For I = 7 To 33
        Cells(I, "M").GoalSeek Goal:=0.3, ChangingCell:=Cells(I, "E")
    Next I

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Upvotes: 1

Views: 1566

Answers (2)

BZ080
BZ080

Reputation: 11

I don't know how to embed code in a comment so I wrote this as an answer..

@CLR: I realised it works now BUT strangely enough, one of the cells turns to 29.96%. The rest become 30% as intended? I then have to manually alter the cell in column E to make the cell in column M turn from 29.96% to 30%.

This is my code at the moment:

Public Sub GoalSeeker()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("DealCalculator")

    For I = 7 To .Cells(.Rows.Count, "M").End(xlUp).Row
        .Cells(I, "M").GoalSeek Goal:=0.3, ChangingCell:=.Cells(I, "E")
    Next I

End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic 
End sub

Also, I have created an Active X button to run this macro in one click but I get a "Compile error. Expected end sub" with the first line of the following code highlighted. Where is my mistake?

Private Sub CommandButton1_Click() 
Public Sub GoalSeeker()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("DealCalculator")

    For I = 7 To .Cells(.Rows.Count, "M").End(xlUp).Row
        .Cells(I, "M").GoalSeek Goal:=0.3, ChangingCell:=.Cells(I, "E")
    Next I

End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic 
End Sub 
End Sub

Upvotes: 0

CLR
CLR

Reputation: 12289

This should do what you need. I've added a With to specify which sheet you're talking about though - you'll need to put the tab name in:

Public Sub GoalSeeker()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Sheets("YourSheetNameHere")

        For I = 7 To .Cells(.Rows.Count, "M").End(xlUp).Row
            .Cells(I, "M").GoalSeek Goal:=0.3, ChangingCell:=.Cells(I, "E")
        Next I

    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Upvotes: 1

Related Questions