PDA

View Full Version : [SOLVED] Goalseek last cell in column, changing last cell in adjacent column



GMSlam
06-18-2018, 10:28 AM
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
06-18-2018, 02:45 PM
Maybe




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

GMSlam
06-19-2018, 07:01 AM
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
06-19-2018, 07:07 AM
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
06-19-2018, 08:50 AM
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
06-20-2018, 06:28 AM
Glad you got it working