Assalamulaikum Wr Wb "Mr Kid"Terima kasih sudah terbantu, tetapi bisa ga kalau
rangenya kebaca automatis sampai data terakhir.Kalo datanya bertambah kan
rangenya berubah, jd harus diganti terus macronya. Mohon dibantu lg
ya..Terimakasih
Wassalamu alaikum Wr Wb
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..
#yiv6879970094 #yiv6879970094 -- #yiv6879970094ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6879970094
#yiv6879970094ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6879970094
#yiv6879970094ygrp-mkp #yiv6879970094hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv6879970094 #yiv6879970094ygrp-mkp #yiv6879970094ads
{margin-bottom:10px;}#yiv6879970094 #yiv6879970094ygrp-mkp .yiv6879970094ad
{padding:0 0;}#yiv6879970094 #yiv6879970094ygrp-mkp .yiv6879970094ad p
{margin:0;}#yiv6879970094 #yiv6879970094ygrp-mkp .yiv6879970094ad a
{color:#0000ff;text-decoration:none;}#yiv6879970094 #yiv6879970094ygrp-sponsor
#yiv6879970094ygrp-lc {font-family:Arial;}#yiv6879970094
#yiv6879970094ygrp-sponsor #yiv6879970094ygrp-lc #yiv6879970094hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6879970094
#yiv6879970094ygrp-sponsor #yiv6879970094ygrp-lc .yiv6879970094ad
{margin-bottom:10px;padding:0 0;}#yiv6879970094 #yiv6879970094actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6879970094
#yiv6879970094activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6879970094
#yiv6879970094activity span {font-weight:700;}#yiv6879970094
#yiv6879970094activity span:first-child
{text-transform:uppercase;}#yiv6879970094 #yiv6879970094activity span a
{color:#5085b6;text-decoration:none;}#yiv6879970094 #yiv6879970094activity span
span {color:#ff7900;}#yiv6879970094 #yiv6879970094activity span
.yiv6879970094underline {text-decoration:underline;}#yiv6879970094
.yiv6879970094attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv6879970094 .yiv6879970094attach div a
{text-decoration:none;}#yiv6879970094 .yiv6879970094attach img
{border:none;padding-right:5px;}#yiv6879970094 .yiv6879970094attach label
{display:block;margin-bottom:5px;}#yiv6879970094 .yiv6879970094attach label a
{text-decoration:none;}#yiv6879970094 blockquote {margin:0 0 0
4px;}#yiv6879970094 .yiv6879970094bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv6879970094
.yiv6879970094bold a {text-decoration:none;}#yiv6879970094 dd.yiv6879970094last
p a {font-family:Verdana;font-weight:700;}#yiv6879970094 dd.yiv6879970094last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6879970094
dd.yiv6879970094last p span.yiv6879970094yshortcuts
{margin-right:0;}#yiv6879970094 div.yiv6879970094attach-table div div a
{text-decoration:none;}#yiv6879970094 div.yiv6879970094attach-table
{width:400px;}#yiv6879970094 div.yiv6879970094file-title a, #yiv6879970094
div.yiv6879970094file-title a:active, #yiv6879970094
div.yiv6879970094file-title a:hover, #yiv6879970094 div.yiv6879970094file-title
a:visited {text-decoration:none;}#yiv6879970094 div.yiv6879970094photo-title a,
#yiv6879970094 div.yiv6879970094photo-title a:active, #yiv6879970094
div.yiv6879970094photo-title a:hover, #yiv6879970094
div.yiv6879970094photo-title a:visited {text-decoration:none;}#yiv6879970094
div#yiv6879970094ygrp-mlmsg #yiv6879970094ygrp-msg p a
span.yiv6879970094yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6879970094
.yiv6879970094green {color:#628c2a;}#yiv6879970094 .yiv6879970094MsoNormal
{margin:0 0 0 0;}#yiv6879970094 o {font-size:0;}#yiv6879970094
#yiv6879970094photos div {float:left;width:72px;}#yiv6879970094
#yiv6879970094photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv6879970094
#yiv6879970094photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6879970094
#yiv6879970094reco-category {font-size:77%;}#yiv6879970094
#yiv6879970094reco-desc {font-size:77%;}#yiv6879970094 .yiv6879970094replbq
{margin:4px;}#yiv6879970094 #yiv6879970094ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv6879970094 #yiv6879970094ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6879970094
#yiv6879970094ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6879970094
#yiv6879970094ygrp-mlmsg select, #yiv6879970094 input, #yiv6879970094 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv6879970094
#yiv6879970094ygrp-mlmsg pre, #yiv6879970094 code {font:115%
monospace;}#yiv6879970094 #yiv6879970094ygrp-mlmsg *
{line-height:1.22em;}#yiv6879970094 #yiv6879970094ygrp-mlmsg #yiv6879970094logo
{padding-bottom:10px;}#yiv6879970094 #yiv6879970094ygrp-msg p a
{font-family:Verdana;}#yiv6879970094 #yiv6879970094ygrp-msg
p#yiv6879970094attach-count span {color:#1E66AE;font-weight:700;}#yiv6879970094
#yiv6879970094ygrp-reco #yiv6879970094reco-head
{color:#ff7900;font-weight:700;}#yiv6879970094 #yiv6879970094ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv6879970094 #yiv6879970094ygrp-sponsor
#yiv6879970094ov li a {font-size:130%;text-decoration:none;}#yiv6879970094
#yiv6879970094ygrp-sponsor #yiv6879970094ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv6879970094
#yiv6879970094ygrp-sponsor #yiv6879970094ov ul {margin:0;padding:0 0 0
8px;}#yiv6879970094 #yiv6879970094ygrp-text
{font-family:Georgia;}#yiv6879970094 #yiv6879970094ygrp-text p {margin:0 0 1em
0;}#yiv6879970094 #yiv6879970094ygrp-text tt {font-size:120%;}#yiv6879970094
#yiv6879970094ygrp-vital ul li:last-child {border-right:none
!important;}#yiv6879970094
