Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help!
Rowan,
You are correct that I want to concatenate the information in cells c:10&c:11 into Comm!B3, it maybe easier just to wrap the text in comm!B3 if that is possible, information that is going into that cell is the customers name (Example Mr. & Mrs John Smith) which may not fit on just C:10. D:12 & D:13 is the address including city and zip and will not fit on just D:12 but needs to be moved to Comm!C3 and it maybe easier just wrap the text in that cell if that is possible (Example 1234 E. Main St Orlando 32808). Column P may have 2 totals P:10-P:11 which could be added together to go into G:3 Commision sheet lay out Col row A B C D E F G H I 1 Date Name Address City Zip Type Collected Percent % 2 Sold of Service Commission Collected 3 4 5 6 etc. 1-31 sheet layout Row COL A B C D E F G H I J 9 Time Of Lead Prospects Name Address Phone Intial Reg Total Sent Sent Arrival" Key Chg PC Install Total K L M N O P 9 "Conv & DWT Intial "Reg "Total Total WDO" Chg" Lawn" Then starts the regular numbers 10 11 12 13 14 15 through 27 I hope this helps and I'm not sure if this can even be done. It sure would be nice if it could! Thank you for your help! "Rowan Drummond" wrote: Sorry, I missed your response in the original thread. I am still not clear on how you want this to work though. It looks like you want to take C10 & C11 into Comm!B3, C12 & C13 into Comm!B4 etc and D10 & D11 into Comm!C3 then D12 & D13 into Comm!C4 etc but then you want to move column P on each daily sheet to the Comm!CoulmnG. You don't mention if you want to concatenate these cells though. If you don't I can't see how you will match up the correct data on each line as you will be using twice as many rows in column G as you do in Column B and C. Regards Rowan jaysphotos wrote: Here is what I'm trying to accomplish. I have a worksheet for each day of the month that has to be filled out on a daily basis, on that sheet in cells C10:C27, each 2 cells have a first and last name in it, therefore the need to concatenate starting in cell B3 and need to be in the individual cells. So yes I would like the c colum to move to each cell in colum B on the commision sheet. Colums D10:D27 would be the same way because it has the address including the city and zip code and would be placed starting in cell C3 on the commision sheet and move down. The colum P on the daliy sheets could be moved to commision sheet starting in colum G3 and move down. The Range of f10:O27 is the critera for the above information to move, if the cells have any numbers in them that is the factor of "sold" therefore the 0. Thank you for the help. "Rowan Drummond" wrote: Hi Your query was not very clear (or maybe it's just me). In the initial request you wanted to concatenate C10 and C11. Now you want to move C10:C27 but I can't see where you want to move this to exactly. Do you want to concatenate all 18 cells together and place the result in B3 in the commisions sheet or are you wanting to move them individually to B3:20. Same for D10:D27 and P10:P27 Try to explain exactly what you want eg: From sheet 1 check range F10:O11. If any cell in that range not blank concatenate C10:C27 and move to B3 on Comm sheet. ..... Move to sheet 2 check range F10:011. If any cell in that range not blank concatenate C10:C27 and move to B4 on Commm sheet. etc Regards Rowan jaysphotos wrote: This worked for one cell and maybe I did not think this through. C10 & C11 are just the first 2 cells in the colum that needs to move. I have C10:C27 that needs to move to the commision sheet in B3, just like you thought below, if 0. I would also like D10:D27 to move to starting in C3. Maybe I'm dreaming but I would like a total(which I could hide that colum) starting with p10:p27 and move it to colum g3 on the commision sheet. Thank you for your help! "Rowan Drummond" wrote: I'm guessing you want to move the data to the commissions sheet starting at cell B3 and then B4, B5 etc. If so try: Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ CountA(sht.Range("F10:O11")) 0 Then comSht.Cells(r, 2).Value = sht.Range("C10").Value _ & " " & sht.Range("C11").Value r = r + 1 End If End If Next sht End Sub Hope this helps Rowan jaysphotos wrote: I have a workbook that has 32 sheets in it 31 of the sheets are the same and the last one is used to track commisions on and do a mail merge to send out thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have anything other than blank I want cells c 10 & 11(these cells have text in them) moved to the commision tracking sheet into cell b3 and it will need to concatenate c 10 & 11. I'am very new to programing. Thank you for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help!
Try something like:
Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long Dim c As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ CountA(sht.Range("F10:O11")) 0 Then For c = 10 To 26 Step 2 comSht.Cells(r, 2).Value = sht.Cells(c, 3).Value _ & " " & sht.Cells(c + 1, 3).Value comSht.Cells(r, 3).Value = sht.Cells(c, 4).Value _ & " " & sht.Cells(c + 1, 4).Value comSht.Cells(r, 7).Value = sht.Cells(c, 16).Value _ + sht.Cells(c + 1, 16).Value r = r + 1 Next c End If End If Next sht comSht.Range("B3:C" & r).WrapText = True End Sub Hope this helps Rowan jaysphotos wrote: Rowan, You are correct that I want to concatenate the information in cells c:10&c:11 into Comm!B3, it maybe easier just to wrap the text in comm!B3 if that is possible, information that is going into that cell is the customers name (Example Mr. & Mrs John Smith) which may not fit on just C:10. D:12 & D:13 is the address including city and zip and will not fit on just D:12 but needs to be moved to Comm!C3 and it maybe easier just wrap the text in that cell if that is possible (Example 1234 E. Main St Orlando 32808). Column P may have 2 totals P:10-P:11 which could be added together to go into G:3 Commision sheet lay out Col row A B C D E F G H I 1 Date Name Address City Zip Type Collected Percent % 2 Sold of Service Commission Collected 3 4 5 6 etc. 1-31 sheet layout Row COL A B C D E F G H I J 9 Time Of Lead Prospects Name Address Phone Intial Reg Total Sent Sent Arrival" Key Chg PC Install Total K L M N O P 9 "Conv & DWT Intial "Reg "Total Total WDO" Chg" Lawn" Then starts the regular numbers 10 11 12 13 14 15 through 27 I hope this helps and I'm not sure if this can even be done. It sure would be nice if it could! Thank you for your help! "Rowan Drummond" wrote: Sorry, I missed your response in the original thread. I am still not clear on how you want this to work though. It looks like you want to take C10 & C11 into Comm!B3, C12 & C13 into Comm!B4 etc and D10 & D11 into Comm!C3 then D12 & D13 into Comm!C4 etc but then you want to move column P on each daily sheet to the Comm!CoulmnG. You don't mention if you want to concatenate these cells though. If you don't I can't see how you will match up the correct data on each line as you will be using twice as many rows in column G as you do in Column B and C. Regards Rowan jaysphotos wrote: Here is what I'm trying to accomplish. I have a worksheet for each day of the month that has to be filled out on a daily basis, on that sheet in cells C10:C27, each 2 cells have a first and last name in it, therefore the need to concatenate starting in cell B3 and need to be in the individual cells. So yes I would like the c colum to move to each cell in colum B on the commision sheet. Colums D10:D27 would be the same way because it has the address including the city and zip code and would be placed starting in cell C3 on the commision sheet and move down. The colum P on the daliy sheets could be moved to commision sheet starting in colum G3 and move down. The Range of f10:O27 is the critera for the above information to move, if the cells have any numbers in them that is the factor of "sold" therefore the 0. Thank you for the help. "Rowan Drummond" wrote: Hi Your query was not very clear (or maybe it's just me). In the initial request you wanted to concatenate C10 and C11. Now you want to move C10:C27 but I can't see where you want to move this to exactly. Do you want to concatenate all 18 cells together and place the result in B3 in the commisions sheet or are you wanting to move them individually to B3:20. Same for D10:D27 and P10:P27 Try to explain exactly what you want eg: From sheet 1 check range F10:O11. If any cell in that range not blank concatenate C10:C27 and move to B3 on Comm sheet. ..... Move to sheet 2 check range F10:011. If any cell in that range not blank concatenate C10:C27 and move to B4 on Commm sheet. etc Regards Rowan jaysphotos wrote: This worked for one cell and maybe I did not think this through. C10 & C11 are just the first 2 cells in the colum that needs to move. I have C10:C27 that needs to move to the commision sheet in B3, just like you thought below, if 0. I would also like D10:D27 to move to starting in C3. Maybe I'm dreaming but I would like a total(which I could hide that colum) starting with p10:p27 and move it to colum g3 on the commision sheet. Thank you for your help! "Rowan Drummond" wrote: I'm guessing you want to move the data to the commissions sheet starting at cell B3 and then B4, B5 etc. If so try: Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ CountA(sht.Range("F10:O11")) 0 Then comSht.Cells(r, 2).Value = sht.Range("C10").Value _ & " " & sht.Range("C11").Value r = r + 1 End If End If Next sht End Sub Hope this helps Rowan jaysphotos wrote: I have a workbook that has 32 sheets in it 31 of the sheets are the same and the last one is used to track commisions on and do a mail merge to send out thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have anything other than blank I want cells c 10 & 11(these cells have text in them) moved to the commision tracking sheet into cell b3 and it will need to concatenate c 10 & 11. I'am very new to programing. Thank you for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help!
This works pretty well however on the commision sheet it pulls over all the
things put in cells C10-C27 regardless of the cells f10-027 having =0. It also places 9 lines on the commision sheet. Thank you for the help. "Rowan Drummond" wrote: Try something like: Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long Dim c As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ CountA(sht.Range("F10:O11")) 0 Then For c = 10 To 26 Step 2 comSht.Cells(r, 2).Value = sht.Cells(c, 3).Value _ & " " & sht.Cells(c + 1, 3).Value comSht.Cells(r, 3).Value = sht.Cells(c, 4).Value _ & " " & sht.Cells(c + 1, 4).Value comSht.Cells(r, 7).Value = sht.Cells(c, 16).Value _ + sht.Cells(c + 1, 16).Value r = r + 1 Next c End If End If Next sht comSht.Range("B3:C" & r).WrapText = True End Sub Hope this helps Rowan jaysphotos wrote: Rowan, You are correct that I want to concatenate the information in cells c:10&c:11 into Comm!B3, it maybe easier just to wrap the text in comm!B3 if that is possible, information that is going into that cell is the customers name (Example Mr. & Mrs John Smith) which may not fit on just C:10. D:12 & D:13 is the address including city and zip and will not fit on just D:12 but needs to be moved to Comm!C3 and it maybe easier just wrap the text in that cell if that is possible (Example 1234 E. Main St Orlando 32808). Column P may have 2 totals P:10-P:11 which could be added together to go into G:3 Commision sheet lay out Col row A B C D E F G H I 1 Date Name Address City Zip Type Collected Percent % 2 Sold of Service Commission Collected 3 4 5 6 etc. 1-31 sheet layout Row COL A B C D E F G H I J 9 Time Of Lead Prospects Name Address Phone Intial Reg Total Sent Sent Arrival" Key Chg PC Install Total K L M N O P 9 "Conv & DWT Intial "Reg "Total Total WDO" Chg" Lawn" Then starts the regular numbers 10 11 12 13 14 15 through 27 I hope this helps and I'm not sure if this can even be done. It sure would be nice if it could! Thank you for your help! "Rowan Drummond" wrote: Sorry, I missed your response in the original thread. I am still not clear on how you want this to work though. It looks like you want to take C10 & C11 into Comm!B3, C12 & C13 into Comm!B4 etc and D10 & D11 into Comm!C3 then D12 & D13 into Comm!C4 etc but then you want to move column P on each daily sheet to the Comm!CoulmnG. You don't mention if you want to concatenate these cells though. If you don't I can't see how you will match up the correct data on each line as you will be using twice as many rows in column G as you do in Column B and C. Regards Rowan jaysphotos wrote: Here is what I'm trying to accomplish. I have a worksheet for each day of the month that has to be filled out on a daily basis, on that sheet in cells C10:C27, each 2 cells have a first and last name in it, therefore the need to concatenate starting in cell B3 and need to be in the individual cells. So yes I would like the c colum to move to each cell in colum B on the commision sheet. Colums D10:D27 would be the same way because it has the address including the city and zip code and would be placed starting in cell C3 on the commision sheet and move down. The colum P on the daliy sheets could be moved to commision sheet starting in colum G3 and move down. The Range of f10:O27 is the critera for the above information to move, if the cells have any numbers in them that is the factor of "sold" therefore the 0. Thank you for the help. "Rowan Drummond" wrote: Hi Your query was not very clear (or maybe it's just me). In the initial request you wanted to concatenate C10 and C11. Now you want to move C10:C27 but I can't see where you want to move this to exactly. Do you want to concatenate all 18 cells together and place the result in B3 in the commisions sheet or are you wanting to move them individually to B3:20. Same for D10:D27 and P10:P27 Try to explain exactly what you want eg: From sheet 1 check range F10:O11. If any cell in that range not blank concatenate C10:C27 and move to B3 on Comm sheet. ..... Move to sheet 2 check range F10:011. If any cell in that range not blank concatenate C10:C27 and move to B4 on Commm sheet. etc Regards Rowan jaysphotos wrote: This worked for one cell and maybe I did not think this through. C10 & C11 are just the first 2 cells in the colum that needs to move. I have C10:C27 that needs to move to the commision sheet in B3, just like you thought below, if 0. I would also like D10:D27 to move to starting in C3. Maybe I'm dreaming but I would like a total(which I could hide that colum) starting with p10:p27 and move it to colum g3 on the commision sheet. Thank you for your help! "Rowan Drummond" wrote: I'm guessing you want to move the data to the commissions sheet starting at cell B3 and then B4, B5 etc. If so try: Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ CountA(sht.Range("F10:O11")) 0 Then comSht.Cells(r, 2).Value = sht.Range("C10").Value _ & " " & sht.Range("C11").Value r = r + 1 End If End If Next sht End Sub Hope this helps Rowan jaysphotos wrote: I have a workbook that has 32 sheets in it 31 of the sheets are the same and the last one is used to track commisions on and do a mail merge to send out thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have anything other than blank I want cells c 10 & 11(these cells have text in them) moved to the commision tracking sheet into cell b3 and it will need to concatenate c 10 & 11. I'am very new to programing. Thank you for your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help!
The code is checking that the cells in F10-O11 are blank as per the
original request. If you want to check that the sum of F10-O27 is 0 then try: Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long Dim c As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ SUM(sht.Range("F10:O11")) < 0 Then For c = 10 To 26 Step 2 comSht.Cells(r, 2).Value = sht.Cells(c, 3).Value _ & " " & sht.Cells(c + 1, 3).Value comSht.Cells(r, 3).Value = sht.Cells(c, 4).Value _ & " " & sht.Cells(c + 1, 4).Value comSht.Cells(r, 7).Value = sht.Cells(c, 16).Value _ + sht.Cells(c + 1, 16).Value r = r + 1 Next c End If End If Next sht comSht.Range("B3:C" & r).WrapText = True End Sub Hope this helps Rowan jaysphotos wrote: This works pretty well however on the commision sheet it pulls over all the things put in cells C10-C27 regardless of the cells f10-027 having =0. It also places 9 lines on the commision sheet. Thank you for the help. "Rowan Drummond" wrote: Try something like: Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long Dim c As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ CountA(sht.Range("F10:O11")) 0 Then For c = 10 To 26 Step 2 comSht.Cells(r, 2).Value = sht.Cells(c, 3).Value _ & " " & sht.Cells(c + 1, 3).Value comSht.Cells(r, 3).Value = sht.Cells(c, 4).Value _ & " " & sht.Cells(c + 1, 4).Value comSht.Cells(r, 7).Value = sht.Cells(c, 16).Value _ + sht.Cells(c + 1, 16).Value r = r + 1 Next c End If End If Next sht comSht.Range("B3:C" & r).WrapText = True End Sub Hope this helps Rowan jaysphotos wrote: Rowan, You are correct that I want to concatenate the information in cells c:10&c:11 into Comm!B3, it maybe easier just to wrap the text in comm!B3 if that is possible, information that is going into that cell is the customers name (Example Mr. & Mrs John Smith) which may not fit on just C:10. D:12 & D:13 is the address including city and zip and will not fit on just D:12 but needs to be moved to Comm!C3 and it maybe easier just wrap the text in that cell if that is possible (Example 1234 E. Main St Orlando 32808). Column P may have 2 totals P:10-P:11 which could be added together to go into G:3 Commision sheet lay out Col row A B C D E F G H I 1 Date Name Address City Zip Type Collected Percent % 2 Sold of Service Commission Collected 3 4 5 6 etc. 1-31 sheet layout Row COL A B C D E F G H I J 9 Time Of Lead Prospects Name Address Phone Intial Reg Total Sent Sent Arrival" Key Chg PC Install Total K L M N O P 9 "Conv & DWT Intial "Reg "Total Total WDO" Chg" Lawn" Then starts the regular numbers 10 11 12 13 14 15 through 27 I hope this helps and I'm not sure if this can even be done. It sure would be nice if it could! Thank you for your help! "Rowan Drummond" wrote: Sorry, I missed your response in the original thread. I am still not clear on how you want this to work though. It looks like you want to take C10 & C11 into Comm!B3, C12 & C13 into Comm!B4 etc and D10 & D11 into Comm!C3 then D12 & D13 into Comm!C4 etc but then you want to move column P on each daily sheet to the Comm!CoulmnG. You don't mention if you want to concatenate these cells though. If you don't I can't see how you will match up the correct data on each line as you will be using twice as many rows in column G as you do in Column B and C. Regards Rowan jaysphotos wrote: Here is what I'm trying to accomplish. I have a worksheet for each day of the month that has to be filled out on a daily basis, on that sheet in cells C10:C27, each 2 cells have a first and last name in it, therefore the need to concatenate starting in cell B3 and need to be in the individual cells. So yes I would like the c colum to move to each cell in colum B on the commision sheet. Colums D10:D27 would be the same way because it has the address including the city and zip code and would be placed starting in cell C3 on the commision sheet and move down. The colum P on the daliy sheets could be moved to commision sheet starting in colum G3 and move down. The Range of f10:O27 is the critera for the above information to move, if the cells have any numbers in them that is the factor of "sold" therefore the 0. Thank you for the help. "Rowan Drummond" wrote: Hi Your query was not very clear (or maybe it's just me). In the initial request you wanted to concatenate C10 and C11. Now you want to move C10:C27 but I can't see where you want to move this to exactly. Do you want to concatenate all 18 cells together and place the result in B3 in the commisions sheet or are you wanting to move them individually to B3:20. Same for D10:D27 and P10:P27 Try to explain exactly what you want eg: From sheet 1 check range F10:O11. If any cell in that range not blank concatenate C10:C27 and move to B3 on Comm sheet. ..... Move to sheet 2 check range F10:011. If any cell in that range not blank concatenate C10:C27 and move to B4 on Commm sheet. etc Regards Rowan jaysphotos wrote: This worked for one cell and maybe I did not think this through. C10 & C11 are just the first 2 cells in the colum that needs to move. I have C10:C27 that needs to move to the commision sheet in B3, just like you thought below, if 0. I would also like D10:D27 to move to starting in C3. Maybe I'm dreaming but I would like a total(which I could hide that colum) starting with p10:p27 and move it to colum g3 on the commision sheet. Thank you for your help! "Rowan Drummond" wrote: I'm guessing you want to move the data to the commissions sheet starting at cell B3 and then B4, B5 etc. If so try: Sub GetData() Dim comSht As Worksheet Dim sht As Worksheet Dim r As Long r = 3 Set comSht = Sheets("Comms") '<<change to name of commissions sheet For Each sht In ThisWorkbook.Worksheets If sht.Name < comSht.Name Then If Application.WorksheetFunction. _ CountA(sht.Range("F10:O11")) 0 Then comSht.Cells(r, 2).Value = sht.Range("C10").Value _ & " " & sht.Range("C11").Value r = r + 1 End If End If Next sht End Sub Hope this helps Rowan jaysphotos wrote: I have a workbook that has 32 sheets in it 31 of the sheets are the same and the last one is used to track commisions on and do a mail merge to send out thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have anything other than blank I want cells c 10 & 11(these cells have text in them) moved to the commision tracking sheet into cell b3 and it will need to concatenate c 10 & 11. I'am very new to programing. Thank you for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|