Akan saya coba,, terima kasih banyakWa alaikum salam..
On Wednesday, 1 April 2015, 4:40, "'Mr. Kid' [email protected]
[belajar-excel]" <[email protected]> wrote:
wa'alaikumussalam wr wb (mbok yao dipanjangin dikit supaya lebih baik)
Goal seek punya 3 elemen pokok, yaitu :
> nilai_target (konstanta bertipe numerik)
> range_changing_cell
> range_set_cell (berisi formula yang link ke range changing cell)
Dengan VBA
Syntax :
range_set_cell.goalseek nilai_target , range_changing_cell
atau jika hasil dari code di atas kurang sesuai atau tidak memuaskan karena
tidak sama dengan hasil dialog window goal seek dari dalam worksheet, bisa
menggunakan :
application.executeexcel4macro _
"GOAL.SEEK(""" _
& alamatR1C1_range_set_cell _
& """," _
& nilai_target _
& ",""" _
& alamatR1C1_range_changing_cell _
& """)"
A. Nilai target konstan
- nilai_target adalah konstan 5000
- range_changing_cell ada di B1:B10
- tange_set_cell (berformula) ada di A1:A10
dim lRowKe as long
for lrowke=1 to 10 'jumlah baris data dari a1:a10
range("a1:a10").cells(lrowke).goalseek 5000 ,
range("b1:b10").cells(lrowke)
next lrowke
atau
dim lRowKe as long
for lrowke=1 to 10 'jumlah baris data dari a1:a10
application.executeexcel4macro _
"GOAL.SEEK(""" _
& range("a1:a10").cells(lrowke).address(referencestyle:=xlR1C1) _
& """," _
& 5000 _
& ",""" _
& range("b1:b10").cells(lrowke).address(referencestyle:=xlR1C1) _
& """)"
next lrowke
B. Nilai target setiap baris data berbeda-beda
- nilai_target ada di range C1:C10
- range_changing_cell ada di B1:B10
- tange_set_cell (berformula) ada di A1:A10
dim lRowKe as long
for lrowke=1 to 10 'jumlah baris data dari a1:a10
range("a1:a10").cells(lrowke).goalseek
range("c1:c10").cells(lrowke).value, range("b1:b10").cells(lrowke)
next lrowke
atau
dim lRowKe as long
for lrowke=1 to 10 'jumlah baris data dari a1:a10
application.executeexcel4macro _
"GOAL.SEEK(""" _
& range("a1:a10").cells(lrowke).address(referencestyle:=xlR1C1) _
& """," _
& range("c1:c10").cells(lrowke).value _
& ",""" _
& range("b1:b10").cells(lrowke).address(referencestyle:=xlR1C1) _
& """)"
next lrowke
Selamat mencoba
wassalamu'alaikum wr wb
Kid
2015-03-30 19:20 GMT+11:00 Dwi Hartanto [email protected] [belajar-excel]
<[email protected]>:
Ass...Mau tanya dong rumus macro utk goalseek sampai data terakhirTrims
Wass..
#yiv0601517080 #yiv0601517080 -- #yiv0601517080ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0601517080
#yiv0601517080ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0601517080
#yiv0601517080ygrp-mkp #yiv0601517080hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0601517080 #yiv0601517080ygrp-mkp #yiv0601517080ads
{margin-bottom:10px;}#yiv0601517080 #yiv0601517080ygrp-mkp .yiv0601517080ad
{padding:0 0;}#yiv0601517080 #yiv0601517080ygrp-mkp .yiv0601517080ad p
{margin:0;}#yiv0601517080 #yiv0601517080ygrp-mkp .yiv0601517080ad a
{color:#0000ff;text-decoration:none;}#yiv0601517080 #yiv0601517080ygrp-sponsor
#yiv0601517080ygrp-lc {font-family:Arial;}#yiv0601517080
#yiv0601517080ygrp-sponsor #yiv0601517080ygrp-lc #yiv0601517080hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0601517080
#yiv0601517080ygrp-sponsor #yiv0601517080ygrp-lc .yiv0601517080ad
{margin-bottom:10px;padding:0 0;}#yiv0601517080 #yiv0601517080actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0601517080
#yiv0601517080activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0601517080
#yiv0601517080activity span {font-weight:700;}#yiv0601517080
#yiv0601517080activity span:first-child
{text-transform:uppercase;}#yiv0601517080 #yiv0601517080activity span a
{color:#5085b6;text-decoration:none;}#yiv0601517080 #yiv0601517080activity span
span {color:#ff7900;}#yiv0601517080 #yiv0601517080activity span
.yiv0601517080underline {text-decoration:underline;}#yiv0601517080
.yiv0601517080attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0601517080 .yiv0601517080attach div a
{text-decoration:none;}#yiv0601517080 .yiv0601517080attach img
{border:none;padding-right:5px;}#yiv0601517080 .yiv0601517080attach label
{display:block;margin-bottom:5px;}#yiv0601517080 .yiv0601517080attach label a
{text-decoration:none;}#yiv0601517080 blockquote {margin:0 0 0
4px;}#yiv0601517080 .yiv0601517080bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0601517080
.yiv0601517080bold a {text-decoration:none;}#yiv0601517080 dd.yiv0601517080last
p a {font-family:Verdana;font-weight:700;}#yiv0601517080 dd.yiv0601517080last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0601517080
dd.yiv0601517080last p span.yiv0601517080yshortcuts
{margin-right:0;}#yiv0601517080 div.yiv0601517080attach-table div div a
{text-decoration:none;}#yiv0601517080 div.yiv0601517080attach-table
{width:400px;}#yiv0601517080 div.yiv0601517080file-title a, #yiv0601517080
div.yiv0601517080file-title a:active, #yiv0601517080
div.yiv0601517080file-title a:hover, #yiv0601517080 div.yiv0601517080file-title
a:visited {text-decoration:none;}#yiv0601517080 div.yiv0601517080photo-title a,
#yiv0601517080 div.yiv0601517080photo-title a:active, #yiv0601517080
div.yiv0601517080photo-title a:hover, #yiv0601517080
div.yiv0601517080photo-title a:visited {text-decoration:none;}#yiv0601517080
div#yiv0601517080ygrp-mlmsg #yiv0601517080ygrp-msg p a
span.yiv0601517080yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0601517080
.yiv0601517080green {color:#628c2a;}#yiv0601517080 .yiv0601517080MsoNormal
{margin:0 0 0 0;}#yiv0601517080 o {font-size:0;}#yiv0601517080
#yiv0601517080photos div {float:left;width:72px;}#yiv0601517080
#yiv0601517080photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv0601517080
#yiv0601517080photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0601517080
#yiv0601517080reco-category {font-size:77%;}#yiv0601517080
#yiv0601517080reco-desc {font-size:77%;}#yiv0601517080 .yiv0601517080replbq
{margin:4px;}#yiv0601517080 #yiv0601517080ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv0601517080 #yiv0601517080ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0601517080
#yiv0601517080ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0601517080
#yiv0601517080ygrp-mlmsg select, #yiv0601517080 input, #yiv0601517080 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv0601517080
#yiv0601517080ygrp-mlmsg pre, #yiv0601517080 code {font:115%
monospace;}#yiv0601517080 #yiv0601517080ygrp-mlmsg *
{line-height:1.22em;}#yiv0601517080 #yiv0601517080ygrp-mlmsg #yiv0601517080logo
{padding-bottom:10px;}#yiv0601517080 #yiv0601517080ygrp-msg p a
{font-family:Verdana;}#yiv0601517080 #yiv0601517080ygrp-msg
p#yiv0601517080attach-count span {color:#1E66AE;font-weight:700;}#yiv0601517080
#yiv0601517080ygrp-reco #yiv0601517080reco-head
{color:#ff7900;font-weight:700;}#yiv0601517080 #yiv0601517080ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv0601517080 #yiv0601517080ygrp-sponsor
#yiv0601517080ov li a {font-size:130%;text-decoration:none;}#yiv0601517080
#yiv0601517080ygrp-sponsor #yiv0601517080ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv0601517080
#yiv0601517080ygrp-sponsor #yiv0601517080ov ul {margin:0;padding:0 0 0
8px;}#yiv0601517080 #yiv0601517080ygrp-text
{font-family:Georgia;}#yiv0601517080 #yiv0601517080ygrp-text p {margin:0 0 1em
0;}#yiv0601517080 #yiv0601517080ygrp-text tt {font-size:120%;}#yiv0601517080
#yiv0601517080ygrp-vital ul li:last-child {border-right:none
!important;}#yiv0601517080
