| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping required
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/comm...e-df9aa8cf680c) Since my VBA knowledge was only from macro recorder (this is my first encounter to UDF) and still waiting for Jacobs reply, thus I havent 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping required
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" wrote: 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/comm...e-df9aa8cf680c) Since my VBA knowledge was only from macro recorder (this is my first encounter to UDF) and still waiting for Jacobs reply, thus I havent 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping required
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" wrote: 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/comm...e-df9aa8cf680c) Since my VBA knowledge was only from macro recorder (this is my first encounter to UDF) and still waiting for Jacobs reply, thus I havent 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping required
oh, sori, lots of typo here, the F7 should read as F2
"Seeker" wrote: 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" wrote: 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/comm...e-df9aa8cf680c) Since my VBA knowledge was only from macro recorder (this is my first encounter to UDF) and still waiting for Jacobs reply, thus I havent 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping required
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 --------------- Jacob Skaria "Seeker" wrote: 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/comm...e-df9aa8cf680c) Since my VBA knowledge was only from macro recorder (this is my first encounter to UDF) and still waiting for Jacobs reply, thus I havent 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping required
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Seeker" wrote: 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/comm...e-df9aa8cf680c) Since my VBA knowledge was only from macro recorder (this is my first encounter to UDF) and still waiting for Jacobs reply, thus I havent 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 |
Linear Mode
