Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
revise CONCATENATE code
Isabelle helped me get my code just as I asked. It concatenate's a number of columns of data and transfers it to a range on the same sheet as the data and then converts the formulas to values. I then added a line to copy that data to another worksheet (in the same workbook).
Is there a way to go straight to the final worksheet with values? Sheets("Search Upr Case-Replace Sec #1") Range("K5"):and down is the final destination. Sub ConcTitle() 'BY_Isb ctrl + shift + H Dim LastRow As Long Dim Ws As Worksheet Dim WSsuc As Worksheet Set Ws = Sheets("Title Generator") Set WSsuc = Sheets("Search Upr Case-Replace Sec #1") LastRow = Ws.Range("C" & Ws.Rows.Count).End(xlUp).Row Ws.Range("AT8:AT" & LastRow).Formula = _ "=C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8&N8&O8&P8" Ws.Range("AT8:AT" & LastRow) = Ws.Range("AT8:AT" & LastRow).Value 'now copy to Sheets("Search Upr Case-Replace Sec #1") K5 WSsuc.Range("K5:K" & LastRow) = Ws.Range("AT8:AT" & LastRow).Value End Sub Thanks. Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
revise CONCATENATE code
Le 2013-02-11 01:52, Howard a écrit : Is there a way to go straight to the final worksheet with values? without placing the formulas in range AT ? if the cell range is very large, the current method is faster, but it requires going through an intermediate column that then can be erase. otherwise it is possible to go through a loop . do you want to go through an intermediate column or go through a loop? isabelle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
revise CONCATENATE code
On Sunday, February 10, 2013 10:52:48 PM UTC-8, Howard wrote:
Isabelle helped me get my code just as I asked. It concatenate's a number of columns of data and transfers it to a range on the same sheet as the data and then converts the formulas to values. I then added a line to copy that data to another worksheet (in the same workbook). Is there a way to go straight to the final worksheet with values? Sheets("Search Upr Case-Replace Sec #1") Range("K5"):and down is the final destination. Sub ConcTitle() 'BY_Isb ctrl + shift + H Dim LastRow As Long Dim Ws As Worksheet Dim WSsuc As Worksheet Set Ws = Sheets("Title Generator") Set WSsuc = Sheets("Search Upr Case-Replace Sec #1") LastRow = Ws.Range("C" & Ws.Rows.Count).End(xlUp).Row Ws.Range("AT8:AT" & LastRow).Formula = _ "=C8&D8&E8&F8&G8&H8&I8&J8&K8&L8&M8&N8&O8&P8" Ws.Range("AT8:AT" & LastRow) = Ws.Range("AT8:AT" & LastRow).Value 'now copy to Sheets("Search Upr Case-Replace Sec #1") K5 WSsuc.Range("K5:K" & LastRow) = Ws.Range("AT8:AT" & LastRow).Value End Sub Thanks. Howard I would be willing to give the LOOP a shot. From this sheet: Set Ws = Sheets("Title Generator") To this sheet Range("K5") and down: Set WSsuc = Sheets("Search Upr Case-Replace Sec #1") The cell range could be around 1000+ rows... and what you supplied does a very decent job. Perhaps just leave it alone? I will heed your advice. Thanks, Isabelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 -Revise Dim | Excel Programming | |||
Need to revise this Sorting code a bit. | Excel Programming | |||
Need to revise formula | Excel Worksheet Functions | |||
Need to revise formula | Excel Worksheet Functions | |||
Need to revise formula | Excel Worksheet Functions |