Goalseek last cell in column, changing last cell in adjacent column

[GMSlam]

Hi,

New to VBA/Excel.

I'm trying to change the value of a cell with goal seek to a value by changing the cell in the column next to it. I cant seem to find out how to set the last range to the last cell in the column.
I attempted placing Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp) directly in along with a number of other attempts and I'm at the point where I just need to walk away and come back.

Sub CalcEndTime()
'
' CalcEndTime Macro
'
' Keyboard Shortcut: Ctrl+t
'
Dim Lastrow As Range
Lastrow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row


Application.CutCopyMode = False
Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp).Offset(0, 1).GoalSeek Goal:=8, ChangingCell:=Range(Lastrow)


End Sub



Error message says RTE 1004: Method 'Range' of object'_Global' failed

[Paul_Hossler]

Maybe

Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp).Offset(0, 1).GoalSeek Goal:=8, ChangingCell:=Worksheets("Hours").Cells(Lastrow,5)

[GMSlam]

Maybe

Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp).Offset(0, 1).GoalSeek Goal:=8, ChangingCell:=Worksheets("Hours").Cells(Lastrow,5)

Same error, I was testing last night, and if I place a regular cell in the last cell say:

Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp).Offset(0, 1).GoalSeek Goal:=8, ChangingCell:=Worksheets("Hours").Cells(A1)

it works using that or any set cell (obviously), so I've come to thing that its something with the Dim/Set?

[GMSlam]

Sorry Actually new error I forgot I had changed the dim several times, when using

Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp).Offset(0, 1).GoalSeek Goal:=8, ChangingCell:=Worksheets("Hours").Cells(Lastrow,5)

I get a new error and highlights on line:

Lastrow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row

Error: RTE 91 Object Variable or With block variable not set

This lead me to add Set to the beginning of line 2:

Dim Lastrow As Range
Set Lastrow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row

which throws RTE 424 Object required on the same Set Lastrow line.

Final code remains there for now:

Sub CalcEndTime()'
' CalcEndTime Macro
'
' Keyboard Shortcut: Ctrl+t
'
Dim Lastrow As Range
Set Lastrow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row


    Application.CutCopyMode = False
    Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp).Offset(0, 1).GoalSeek Goal:=8, ChangingCell:=Range(Lastrow, 5)


End Sub

[GMSlam]

Maybe

Worksheets("Hours").Cells(Rows.Count, 5).End(xlUp).Offset(0, 1).GoalSeek Goal:=8, ChangingCell:=Worksheets("Hours").Cells(Lastrow,5)

Upon further review this did work Paul. I had made so many changes in the meantime that I messed up something else somewhere, I restarted from scratch with this input and it worked. Thank you!

[Paul_Hossler]

Glad you got it working