Goal Seek in a VBA Loop Calculation issue for some cells

[smd747]

I use Goal seek function in excel an wanted to automate it.

I have my goal hours in cell “G1” where I want to get all by picks completed by. In this example 5 hours.

I have in “ F3:F21” formula =$C$3*$E$3 and in cell “G3:G21” = =IFERROR(IF(D3>=C3,D3/F3,1),0)

When I use the Goal seek what if Analysis and enter the following :

Set Cell: G3
To Value: G1
By changing cell E3
When I do this for each cell everything calculates fine. It gives me how many pickers I would need to pick the Que Groups instructions for the Goal Hours to Finish Picks.

When I run my VBA look any numbers less then Hourly required to meet minimum are way off .

Here is both loops I tried:
UpdatedGoalSeek

	A	B	C	D	E	F	G	H
1				Goal Hours to Finish Picks	5
2	Que Group	Min Requirement	Hourly Required to Meet	QueGroup Instructions Breakdown	No of Pickers assigned	Hourly Picks Per Team	Meeting Min Hours Req to Complete
3	DSLA	162	27	27	1.0	27	1.0
4	ITTTQ	162	27	196	1.0	27	7.3
5	T300	162	27	110	1.0	27	4.1
6	T500	162	27	160	1.0	27	5.9
7	T600	162	27	129	1.0	27	4.8
8	T700	140	23	140	1.0	23	6.0
9	TBULK	114	19	92	1.0	19	4.8
10	TDK2D	326	54	4	1.0	54	1.0
11	TMEZZ	326	54	656	1.0	54	12.1
12	TPRCK	162	27	112	1.0	27	4.1
13	TTURR	120	20	49	1.0	20	2.5
14	TUMEZ	326	54	53	1.0	54	1.0
15	TYFIT	80	13	22	1.0	13	1.7
16	TYPIP	80	13	7	1.0	13	1.0
17	TYTNK	80	13	1	1.0	13	1.0
18					1.0		0.0
19					1.0		0.0
20					1.0		0.0
21					1.0		0.0
22

<tbody>
</tbody>

Sub PickOptimizerGoalSeek()
Dim X As Integer
Dim i As Integer
X = Range("G1").Value

For i = 3 To 21
If Range("D" & i).Value <= Range("C" & i).Value Then
Range("G" & i).GoalSeek Goal:=X, ChangingCell:=Range("E" & i)
End If
    Next i
End Sub

Excel

and then this code :

Sub PickGoalSeek()
    Dim i As Integer
    Dim j As Double
    j = Range("G1").Value
    For i = 3 To 21
        If Range("D" & i).Value <> 0 Then
            Range("G" & i).GoalSeek Goal:=j, ChangingCell:=Range("E" & i)
End If
    Next i
End Sub

tables to the web >> Excel Jeanie
HTML 4

Any one see what I am doing wrong with my code.
Do I need to code if, say D3 is less than C3 do not goal seek because any number less than gives me wrong long numbers.

Any help and direction as to what I am doing wrong is appreciated. Thanks

 

[smd747]

I use Goal seek function in excel an wanted to automate it.

I have my goal hours in cell “G1” where I want to get all by picks completed by. In this example 5 hours.

I have in “ F3:F21” formula =$C$3*$E$3 and in cell “G3:G21” = =IFERROR(IF(D3>=C3,D3/F3,1),0)

When I use the Goal seek what if Analysis and enter the following :

Set Cell: G3
To Value: G1
By changing cell E3
When I do this for each cell everything calculates fine. It gives me how many pickers I would need to pick the Que Groups instructions for the Goal Hours to Finish Picks.

When I run my VBA look any numbers less then Hourly required to meet minimum are way off .

Here is both loops I tried:
UpdatedGoalSeek

	A	B	C	D	E	F	G	H
1				Goal Hours to Finish Picks	5
2	Que Group	Min Requirement	Hourly Required to Meet	QueGroup Instructions Breakdown	No of Pickers assigned	Hourly Picks Per Team	Meeting Min Hours Req to Complete
3	DSLA	162	27	27	1.0	27	1.0
4	ITTTQ	162	27	196	1.0	27	7.3
5	T300	162	27	110	1.0	27	4.1
6	T500	162	27	160	1.0	27	5.9
7	T600	162	27	129	1.0	27	4.8
8	T700	140	23	140	1.0	23	6.0
9	TBULK	114	19	92	1.0	19	4.8
10	TDK2D	326	54	4	1.0	54	1.0
11	TMEZZ	326	54	656	1.0	54	12.1
12	TPRCK	162	27	112	1.0	27	4.1
13	TTURR	120	20	49	1.0	20	2.5
14	TUMEZ	326	54	53	1.0	54	1.0
15	TYFIT	80	13	22	1.0	13	1.7
16	TYPIP	80	13	7	1.0	13	1.0
17	TYTNK	80	13	1	1.0	13	1.0
18					1.0		0.0
19					1.0		0.0
20					1.0		0.0
21					1.0		0.0
22

<TBODY>
</TBODY>

Sub PickOptimizerGoalSeek()
Dim X As Integer
Dim i As Integer
X = Range("G1").Value

For i = 3 To 21
If Range("D" & i).Value <= Range("C" & i).Value Then
Range("G" & i).GoalSeek Goal:=X, ChangingCell:=Range("E" & i)
End If
    Next i
End Sub

Excel

and then this code :

Sub PickGoalSeek()
    Dim i As Integer
    Dim j As Double
    j = Range("G1").Value
    For i = 3 To 21
        If Range("D" & i).Value <> 0 Then
            Range("G" & i).GoalSeek Goal:=j, ChangingCell:=Range("E" & i)
End If
    Next i
End Sub

tables to the web >> Excel Jeanie
HTML 4

Any one see what I am doing wrong with my code.
Do I need to code if, say D3 is less than C3 do not goal seek because any number less than gives me wrong long numbers.

Any help and direction as to what I am doing wrong is appreciated. Thanks

I am one step closer with this updated code. Still run in to problems with low numbers and zero's:

Sub PickOptimizerGoalSeek()
Dim X As Integer
Dim i As Integer
X = Range("G1").Value
For i = 5 To 23
If Range("D" & i).Value > Range("C" & i).Value Then
Range("G" & i).GoalSeek Goal:=X, ChangingCell:=Range("E" & i)
End If
    Next i
    
End Sub

Does any one see where I can improve this code? Thanks

 

[smd747]

Here is my final working code for anyone interested or was following thread:

Sub PickOptimizerGoalSeek()
Dim X As Integer
Dim i As Integer
ActiveSheet.Unprotect Password:=""
Dim RESET As Integer
'Reset Pickers to default
For RESET = 5 To 24
If Range("E" & RESET).Value >= 1 Then
Range("F" & RESET).Value = 1
 Else
Range("F" & RESET).Value = 0
'    Range("F5:F24").Value = 1
End If
Next RESET
'Start the Pick Optimizer with Goal Seek
X = Range("H1").Value
For i = 5 To 24
If Range("E" & i).Value > Range("D" & i).Value Then
Range("H" & i).GoalSeek Goal:=X, ChangingCell:=Range("F" & i)
End If
    Next i
ActiveSheet.Protect Password:="", Contents:=True, Scenarios:=True, DrawingObjects:=True
End Sub