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
|