More efficient code for a copy loop

[lucky245]

The below code works perfectly but when doing 20 odd thousand rows it takes a good bit of time so I was just seeing if there was a more efficient method of doing same thing.

Sub CopySensToReleventCell() '
Dim i As Long
Dim y As Integer
cgws.Select 'selecting results worksheet
For i = 2 To CalcRowscgr ' For statement from 2 to total number of rows in sheet
If Cells(i, 2) = "" Then 'column 9 should have a value but sometimes wont for various reasons
Cells(i, 2) = "NR" 'Put NR in where there was a blank because result maybe too weak
End If
For y = 3 To cgws.UsedRange.Columns.Count
' starts at col 3 and counts number of columns (currently 25) incase of additional column required
If Cells(i, 1) = Cells(1, y) Then 'if test code in column matched top row of same process
Cells(i, y) = Cells(i, 2) ' copy into cell under relevant result
Exit For ‘exits y for loop
End If
Next y
Next i
End Sub

Eg below

Start

Code	Result	AMI	AMO	AMP	CAX	CTP	CNO	DNo
AMI	S
AMP	s
CAX	R
DNo	NR

<tbody>
</tbody>

End result

Code	Result	AMI	AMO	AMP	CAX	CTP	CNO	DNo
AMI	S	S
AMP	s			s
CAX	R				R
DNo	NR							NR

<tbody>
</tbody>

 

[Joe4]

Do you really need code to do this? This can be done pretty easily with an Excel formula.
Assuming that your data start in cell A1 with the title, so the first cell that you are filling in is C2, enter this formula in C2:

=IF($A2=C$1,$B2,"")

Then, copy this formula down in all the blank cells (down to I5).

The VBA to do that would look something like this:

Sub MyFillMacro()

    Dim lastCol As Long
    Dim lastRow As Long
    
'   Find last column in row 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
'   Find last row in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Populate all cells starting from C2 going to the end
    Range(Cells(2, 3), Cells(lastRow, lastCol)).FormulaR1C1 = "=IF(RC1=R1C,RC2,"""")"

End Sub

If you want to replace all those formulas with just the hard-coded values, then just add this one row to the end of the code above:

    Range(Cells(2, 3), Cells(lastRow, lastCol)).Value = Range(Cells(2, 3), Cells(lastRow, lastCol)).Value