Loop formula in Excel VBA

[Anantha Krishna]

Hello Everyone,

I kindly request you to provide solution for the below situation.

in Excel i have data with serial number in column L and for that status will be on the Column A, like this there are multiple serial numbers.

I want like this

If serial number in cell L2 and status in cell A5 then that cell A5 value should come to the column Q2.

i am using below code, but this is happening for the first serial number only, i want like this to happened for all the serial number.

i have attached excel file for your reference, in sheet1 i have raw data, and in sheet2 i have mention manual the status in Q column how i want to be.

Sub comment()
Dim lng As Long
Dim X As Long
'Determine Last Row with data in Column A
lng = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows in reverse order
  For X = lng To 1 Step -1
Cells(2, 17) = Evaluate("Offset(L2, 3, -11)")
Next X
End Sub

Kindly provide solution for this.

Regards,
Anantha

 

[Fluff13]

How about

Sub Anantha()
   Dim Cl As Range
  
   For Each Cl In Range("L:L").SpecialCells(xlConstants).Areas
      Cl(2, 1).Offset(, 5).Value = Cl(2, 1).Offset(2, -11).Value
   Next Cl
End Sub

 

[p45cal]

Try:

Sub blah()
lng = Range("A" & Rows.Count).End(xlUp).Row
For Each cll In Range("L1:L" & lng).Cells
  If cll.Value = "Serial #" And cll.Offset(1).Value <> "" Then Cells(cll.Row + 1, "Q").Value = Cells(cll.Row + 4, "A").Value
Next cll
End Sub

 

[Anantha Krishna]

Try:

Sub blah()
lng = Range("A" & Rows.Count).End(xlUp).Row
For Each cll In Range("L1:L" & lng).Cells
  If cll.Value = "Serial #" And cll.Offset(1).Value <> "" Then Cells(cll.Row + 1, "Q").Value = Cells(cll.Row + 4, "A").Value
Next cll
End Sub

Thank you so much p45cal it is working

 

[Anantha Krishna]

How about

Sub Anantha()
   Dim Cl As Range
 
   For Each Cl In Range("L:L").SpecialCells(xlConstants).Areas
      Cl(2, 1).Offset(, 5).Value = Cl(2, 1).Offset(2, -11).Value
   Next Cl
End Sub

Thank you so much Fluff13 for your reply.

 

[Fluff13]

You're welcome & thanks for the feedback