Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from different column
I have data such as:
GLN Master SAP 1234 3333 4444 4567 7878 8888 9999 6789 4545 3535 This was created from a pivot. At each change in GLN (Col A) I need it to select the first value in SAP (Col C) and paste in all rows for the group in the Master column (Col B) The result like: 1234 3333 3333 3333 4444 4567 7878 7878 7878 8888 7878 9999 6789 4545 4545 4545 3535 the number of rows for each GLN group will vary - usually 2 but sometimes more. Thanks for you help!! -- maryj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from different column
On May 6, 8:56*am, maryj wrote:
I have data such as: GLN * * * *Master * * *SAP 1234 * * * * * * * * * * *3333 * * * * * * * * * * * * * * *4444 4567 * * * * * * * * * * *7878 * * * * * * * * * * * * * * *8888 * * * * * * * * * * * * * * *9999 6789 * * * * * * * * * * *4545 * * * * * * * * * * * * * * *3535 This was created from a pivot. At each change in GLN (Col A) I need it to select the first value in SAP (Col C) and paste in all rows for the group in the Master column (Col B) The result like: 1234 * * 3333 * * * * *3333 * * * * * * 3333 * * * * *4444 4567 * * 7878 * * * * *7878 * * * * * * 7878 * * * * *8888 * * * * * * 7878 * * * * *9999 6789 * * 4545 * * * * *4545 * * * * * * 4545 * * * * *3535 the number of rows for each GLN group will vary - usually 2 but sometimes more. Thanks for you help!! -- maryj MaryJ, See the code below. Best, Matthew Herbert Sub FillMaster() Dim lngCnt As Long Dim rngGLN As Range Dim rngSAP As Range Dim rngCell As Range Dim intOffset As Integer 'assuming "SAP" is in C1 lngCnt = Range(Range("C1"), Range("C1").End(xlDown)).Rows.Count 'offset column from GLN intOffset = 2 'create range object to loop through Set rngGLN = Range(Range("A2"), Range("A" & lngCnt)) 'loop through each cell in GLN For Each rngCell In rngGLN.Cells 'if the cell is not blank then get the corresponding SAP value If rngCell < "" Then Set rngSAP = rngCell.Offset(0, intOffset) End If 'output the SAP value into Master rngCell.Offset(0, intOffset - 1).Value = rngSAP.Value Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from different column
Perfect!!!
Thanks so much! -- maryj " wrote: On May 6, 8:56 am, maryj wrote: I have data such as: GLN Master SAP 1234 3333 4444 4567 7878 8888 9999 6789 4545 3535 This was created from a pivot. At each change in GLN (Col A) I need it to select the first value in SAP (Col C) and paste in all rows for the group in the Master column (Col B) The result like: 1234 3333 3333 3333 4444 4567 7878 7878 7878 8888 7878 9999 6789 4545 4545 4545 3535 the number of rows for each GLN group will vary - usually 2 but sometimes more. Thanks for you help!! -- maryj MaryJ, See the code below. Best, Matthew Herbert Sub FillMaster() Dim lngCnt As Long Dim rngGLN As Range Dim rngSAP As Range Dim rngCell As Range Dim intOffset As Integer 'assuming "SAP" is in C1 lngCnt = Range(Range("C1"), Range("C1").End(xlDown)).Rows.Count 'offset column from GLN intOffset = 2 'create range object to loop through Set rngGLN = Range(Range("A2"), Range("A" & lngCnt)) 'loop through each cell in GLN For Each rngCell In rngGLN.Cells 'if the cell is not blank then get the corresponding SAP value If rngCell < "" Then Set rngSAP = rngCell.Offset(0, intOffset) End If 'output the SAP value into Master rngCell.Offset(0, intOffset - 1).Value = rngSAP.Value Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data from 205 columns to 1 column | Excel Worksheet Functions | |||
copying data to a named column on a different worksheet | Excel Discussion (Misc queries) | |||
Need help copying data from one column to specific columns | Excel Programming | |||
Copying Data into Column with Existing Data | Excel Discussion (Misc queries) | |||
Copying a formula in a blank column as far as data in previous column | Excel Programming |