• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loop formula in Excel VBA

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.

Code:
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
 

Attachments

Last edited by a moderator:

Fluff13

Active Member
How about
Code:
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

Well-Known Member
Try:
Code:
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
 
Try:
Code:
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
 
Top