looping required

[Seeker]

Dear Volunteers,
I posted a similar thread on yesterday and Jacob Skaria was so kind in
providing an UDF to me.
(http://www.microsoft.com/office/com...&p=1&tid=070db63a-4e16-4e96-bc6e-df9aa8cf680c)
Since my VBA knowledge was only from macro recorder (this is my first
encounter to UDF) and still waiting for Jacob’s reply, thus I haven’t try his
UDF yet.
I went on my sheet and twisted in setting and macro arrangement, now the
macro is done and able to capture one set of data. However, this code is too
lengthy and I also need it not only capture one set of data, but may be two
or four data at a time, any idea please?

Data in Sht DataInput has three ranges of input areas,
Area one produce 4 sets data
Area two produce 2 sets data
Area three produce 1 set data
I have done subA() capture 4 sets data, subB() capture 2 set data and subC()
capture 1 set data, all data are appended to Sht Data in
ColA= today()
ColB= another Date ‘<---when capture 4 sets data, produce two different dates
ColC= curreny symbol ‘<----- has 10 different currencies
ColD= amount
ColE= comment
ColF= client ‘<----------- 17 different names

Now I need to pick data from Sht Data and show them from row 30 onward in
Sht DataInput, so users will know immediately if they have any incorrect data
input or not. In Sht DataInput, Row 28 is the heading line from ColA to ColQ,
each cell contains combination of Sht Data ColC+space+ColF.
The Criteria are :
1) Sht Data ColB = today() and ColC = USD or HKD
2) Sht Data ColB = Tom ‘=(IF(WEEKDAY((Date+1),2)=6,Date+3,Date+1)) and
ColC<>USD or HKD
If meet above then
Append data from Sht Data ColD to the correct Column in Sht DataInput base
on Sht DataInput Col*'s heading = Sht Data ColC & ColF, then capture contents
in Sht Data ColE as comment on the same cell.

Expected result on Sht Data as follow (with each amount has a comment box
attached):
Col A Col B Col C … Col R
row 29 aa bb cc rr
row 30 1,000 500 30
row 31 800
Tks in advance.
Rgds

 

[Seeker]

A supplement to my earlier thread.
The criteria on Sht Data ColB is either 1) or 2), any date not fall on today
or "tom", will ignor them.

 

[Seeker]

A piece of my work where "F7" is the cell with date, this code was copied
from here & there and I added the "If Then" to adopt the requirement.
Obviously the replicated of If Then argement is not suitable to handle the
task (only picking 1 set of data require 17 times of If Then).

F7 is location of date in input area of one set data,
F11 is location of date in input area of two set data,
C9 & C10 are location of dates in input area of four set data
All these dates are appended to ColB in Sht Data

Sheets("Data").Select
Range("E65536").End(xlUp).Select

Dim tst As String
tst = ActiveCell.Value

Dim Tom As Date 'IF(WEEKDAY((Date+1),2)=6,Date+3,Date+1)) ---- tomorrow
but skip Sat & Sun
If Weekday((Date + 1), 2) = 6 Then
Tom = Date + 3
Else
Tom = Date + 1
End If

Sheets("DataInput").Select
If Range("F6").Value = "BNS" And Range("F2") = Date Then 'Value Today
Sheets("Data").Select
Lastrow = Range("D" & Rows.Count).End(xlUp).Offset(0, 0).Row
Range("D" & Lastrow).Select
Selection.Copy
Sheets("DataInput").Select
Range("J65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.NoteText tst
Else
If Range("F6").Value = "BoNY" And Range("F2") = Tom Then 'Value Tomorrow
........

 

[Seeker]

oh, sori, lots of typo here, the F7 should read as F2

 

[Jacob Skaria]

I have responded to your previous post...

The macro will get data to the activesheet row2 with matching values from
colD and comments (if any). Everytime you run the macro it will clear row2
data and comments if any and redo the whole thing again..

If this post helps click Yes

 

[Seeker]

Hi Jacob,
Thanks again for your code. Your assistance is highly appreciated,
especially to a tyro like me.
The purpose of the sheet is keep track on movement activities on funds, so
additional data will be added during the day, hence I need to append any
additional amount to appropriate column instead of only using one row for
temporary record tracing.
Before received your reply, I have been working with my existing macro for
more than 11 hours on today and am able to get the amount together with
comment from sht 1 ColE to Sht 2. My happiness on successful macro done only
last for a minute because problems arose one after the other and obviously
there are plenty of rooms to improve if it is structured by an expert like
you.
I have module2 & 3 also doing the same things but they extract 2 rows and 4
rows instead of which I realized that what I have done on select data for
copy/paste function are not capable to handle. Would you like to take a look
on my existing book and point me to the right direction please?
Thanks again for your help.
Rgds