On May 6, 8:56*am, maryj <ma...@discussions.microsoft.com> 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 groupin
> 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
|
Similar Threads
