Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment column in loop
Hello, I'm trying to create a loop which selects data in multipl
worksheets and pastes it into another sheet. It would be doing tw columns at a time. In other word, the first iteration would be pastin to columns 1-2, the 2nd 3-4, 3rd 5-6, etc. However, Excel uses letter for their column headers. How can can I tell it to increment the colum on every iteration of the loop? Below is a (probably bad) illustration of what I want to do: Worksheet Code ------------------- AA AA A ------------------- +Worksheet Code ------------------- BB BB B ------------------- +Worksheet Code ------------------- CC CC C ------------------- =Different Shee Code ------------------- AABBCC AABBCC AABBC ------------------- -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment column in loop
You could try writing a function that changes column numbers t
letters. So, if the function recieves 27 as a parameter it would return th string "AA". It would look something like this. 1 would generate "A" 3 .... "C" 26 ...."X" 27 ....."AA" And so on. The iteration would be easier I think. Don't know if that helps...... -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment column in loop
That would work if I knew how to convert them to letters like that
-- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment column in loop
the cells object can use column numbers
for i = 1 to 10 step 2 msgbox cells(1,i).Resize(2).Address & " - " & _ Columns(i).Resize(,2).Address Next as an example. So let us say you want to consolidate data to a sheet named Data from 4 other sheets Dim varr as Variant, j as long, i as long Dim sh as Worksheet varr = Array("Sheet1","Sheet2","Sheet3","Sheet4") numSheets = ubound(varr) - lbound(varr) + 1 j = -1 for i = lbound(varr) to ubound(varr) j = j + 2 set sh = Worksheets(varr(i)) sh.Columns(1).Resize(,2).copy _ destination:=worksheets("Data").Cells(1,j) Next -- Regards, Tom Ogilvy "RyanVM " wrote in message ... That would work if I knew how to convert them to letters like that. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment column in loop
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I increment a column in Excel | Excel Discussion (Misc queries) | |||
Increment column on drag down | Excel Discussion (Misc queries) | |||
How to increment within For Next Loop | Excel Discussion (Misc queries) | |||
increment a column based on information in another column | New Users to Excel | |||
Increment through For Next loop | Excel Programming |