I threw this together quickly, so it might could use some more comments and explanations.
Basically the technique I used is this:
Since I don't know how large the Names list will become, or what sort order.I like to use a Dictionary Object to load the names and associated Row numbers.
I also "assume" that the months will be in chronological order. That is: January-Decemberand not some random order.
So.. I created couple of function.One to load the Dictionary object with the names/rowsand another that you pass the IDname, Month, and value.I check to see if the IDname is in the Dictionary, and use that to determine the row.the month "string" I convert to a true date, then convert the date to a month "number" and use that to determine the column (month number + 1)
Then, simply add the new value to the Totals value.
You MIGHT consider checking the value in the "paid" cell at the beginning of the double-click eventto ensure that it starts as blank.Otherwise, if you double-click the same cell multiple times, it will keep adding the value to the Totals sheet.
But then again, maybe that's what you want?
let me know if you have any questions. Paul-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
On Tuesday, August 15, 2017 9:50 PM, "grahamfellows2002@yahoo.co.uk [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:
Hi Experts,Firstly, thanks to Lisa, the Group Moderator, for adding myWorkbook entitled AutoTransfer to the Group's Files Needing Help list. It helpsme enormously to explain what I'm trying to do.On the first sheet (INPUT) there are 4 columns, A-D, andheaded ID, Month, Amount, and Paid respectively. There is also aBeforeDoubleClick sheet macro which lets me, after double clicking on a cell incolumn D copy the amount in the same row from column C.On the next sheet (TOTALS) there is a 2D matrix made up of anamed range called months, which extends from B1:M1, and another named rangecalled names which extends down from A2:A43When double clicking on Column D of sheet INPUT, in additionto copying the amount from column C to D I would also like to add it to theexisting amount in the correct cell on sheet TOTALS using the name and month ofsheet INPUT Columns A and B as search parameters.So, for instance if I double clicked INPUT D9 then 80 wouldbe copied over from C9 but I would also like the 80 to be added to TOTALS I 9,which is for Grace: August.I can get round it using formulas but I think VBA wouldoffer a more elegant solution but my knowledge doesn't extent that far and Ican't find anything of the web that would help thus I'm throwing myself on yourmercy. RegardsGraham
#yiv1621550080 #yiv1621550080 -- #yiv1621550080ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1621550080 #yiv1621550080ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1621550080 #yiv1621550080ygrp-mkp #yiv1621550080hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1621550080 #yiv1621550080ygrp-mkp #yiv1621550080ads {margin-bottom:10px;}#yiv1621550080 #yiv1621550080ygrp-mkp .yiv1621550080ad {padding:0 0;}#yiv1621550080 #yiv1621550080ygrp-mkp .yiv1621550080ad p {margin:0;}#yiv1621550080 #yiv1621550080ygrp-mkp .yiv1621550080ad a {color:#0000ff;text-decoration:none;}#yiv1621550080 #yiv1621550080ygrp-sponsor #yiv1621550080ygrp-lc {font-family:Arial;}#yiv1621550080 #yiv1621550080ygrp-sponsor #yiv1621550080ygrp-lc #yiv1621550080hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1621550080 #yiv1621550080ygrp-sponsor #yiv1621550080ygrp-lc .yiv1621550080ad {margin-bottom:10px;padding:0 0;}#yiv1621550080 #yiv1621550080actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1621550080 #yiv1621550080activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1621550080 #yiv1621550080activity span {font-weight:700;}#yiv1621550080 #yiv1621550080activity span:first-child {text-transform:uppercase;}#yiv1621550080 #yiv1621550080activity span a {color:#5085b6;text-decoration:none;}#yiv1621550080 #yiv1621550080activity span span {color:#ff7900;}#yiv1621550080 #yiv1621550080activity span .yiv1621550080underline {text-decoration:underline;}#yiv1621550080 .yiv1621550080attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1621550080 .yiv1621550080attach div a {text-decoration:none;}#yiv1621550080 .yiv1621550080attach img {border:none;padding-right:5px;}#yiv1621550080 .yiv1621550080attach label {display:block;margin-bottom:5px;}#yiv1621550080 .yiv1621550080attach label a {text-decoration:none;}#yiv1621550080 blockquote {margin:0 0 0 4px;}#yiv1621550080 .yiv1621550080bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1621550080 .yiv1621550080bold a {text-decoration:none;}#yiv1621550080 dd.yiv1621550080last p a {font-family:Verdana;font-weight:700;}#yiv1621550080 dd.yiv1621550080last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1621550080 dd.yiv1621550080last p span.yiv1621550080yshortcuts {margin-right:0;}#yiv1621550080 div.yiv1621550080attach-table div div a {text-decoration:none;}#yiv1621550080 div.yiv1621550080attach-table {width:400px;}#yiv1621550080 div.yiv1621550080file-title a, #yiv1621550080 div.yiv1621550080file-title a:active, #yiv1621550080 div.yiv1621550080file-title a:hover, #yiv1621550080 div.yiv1621550080file-title a:visited {text-decoration:none;}#yiv1621550080 div.yiv1621550080photo-title a, #yiv1621550080 div.yiv1621550080photo-title a:active, #yiv1621550080 div.yiv1621550080photo-title a:hover, #yiv1621550080 div.yiv1621550080photo-title a:visited {text-decoration:none;}#yiv1621550080 div#yiv1621550080ygrp-mlmsg #yiv1621550080ygrp-msg p a span.yiv1621550080yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1621550080 .yiv1621550080green {color:#628c2a;}#yiv1621550080 .yiv1621550080MsoNormal {margin:0 0 0 0;}#yiv1621550080 o {font-size:0;}#yiv1621550080 #yiv1621550080photos div {float:left;width:72px;}#yiv1621550080 #yiv1621550080photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv1621550080 #yiv1621550080photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1621550080 #yiv1621550080reco-category {font-size:77%;}#yiv1621550080 #yiv1621550080reco-desc {font-size:77%;}#yiv1621550080 .yiv1621550080replbq {margin:4px;}#yiv1621550080 #yiv1621550080ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1621550080 #yiv1621550080ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1621550080 #yiv1621550080ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1621550080 #yiv1621550080ygrp-mlmsg select, #yiv1621550080 input, #yiv1621550080 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1621550080 #yiv1621550080ygrp-mlmsg pre, #yiv1621550080 code {font:115% monospace;}#yiv1621550080 #yiv1621550080ygrp-mlmsg * {line-height:1.22em;}#yiv1621550080 #yiv1621550080ygrp-mlmsg #yiv1621550080logo {padding-bottom:10px;}#yiv1621550080 #yiv1621550080ygrp-msg p a {font-family:Verdana;}#yiv1621550080 #yiv1621550080ygrp-msg p#yiv1621550080attach-count span {color:#1E66AE;font-weight:700;}#yiv1621550080 #yiv1621550080ygrp-reco #yiv1621550080reco-head {color:#ff7900;font-weight:700;}#yiv1621550080 #yiv1621550080ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1621550080 #yiv1621550080ygrp-sponsor #yiv1621550080ov li a {font-size:130%;text-decoration:none;}#yiv1621550080 #yiv1621550080ygrp-sponsor #yiv1621550080ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1621550080 #yiv1621550080ygrp-sponsor #yiv1621550080ov ul {margin:0;padding:0 0 0 8px;}#yiv1621550080 #yiv1621550080ygrp-text {font-family:Georgia;}#yiv1621550080 #yiv1621550080ygrp-text p {margin:0 0 1em 0;}#yiv1621550080 #yiv1621550080ygrp-text tt {font-size:120%;}#yiv1621550080 #yiv1621550080ygrp-vital ul li:last-child {border-right:none !important;}#yiv1621550080
[Non-text portions of this message have been removed]
Posted by: Paul Schreiner <schreiner_paul@att.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar