how to stop this loop....

[vimalanathk]

Hi,

Below is my script and its not stopping once the data is over.....Please help me on this and correct me where I am going wrong

 
Sub test()

Dim row As Long
    row = 2
    
    Range("K2").Select
    
    Do
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=+RC[-11]"
    ActiveCell.Offset(0, -1).Select
    Selection.End(xlDown).Select
    
 Loop Until Cells(row, 3) = "Powered by GL Compliance Manager"

End Sub

[HaHoBe]

Hi, vimalanathk,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[HaHoBe]

Hi, vimalanathk,,

you compare the contents of Range(C2) constantly - is this calculated by formula or a constant value found in Column C? Maybe try it like this or post a sample of your workbook:

Sub test()

Range("K2").Select

Do Until Cells(ActiveCell.row, 3) = "Powered by GL Compliance Manager"
  With ActiveCell
    .Offset(0, 1).FormulaR1C1 = "=+RC[-11]"
    .End(xlDown).Select
  End With
Loop

End Sub

Alternative code:

Sub test2()
Dim rngFound As Range

Set rngFound = Columns(3).Find(what:="Powered by GL Compliance Manager", LookAt:=xlWhole)
If Not rngFound Is Nothing Then
  Range("L2:L" & rngFound.row).FormulaR1C1 = "=RC[-11]"
End If

Set rngFound = Nothing

End Sub

You should consider any other variable as Row is part of the VBA vocabulary.

Ciao,
Holger

[vimalanathk]

I tried all the codes given in replies but the loop is not stopping and its going to end of excel sheet.

Please find the example sheet of my data and help me on the same.

Regards,
Vimala K

[HaHoBe]

Hi, Vimala K,

no need for any loop IMO:

Sub EF972535()
On Error Resume Next
Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Offset(0, 1).FormulaR1C1 = "=RC[-11]"
If Err <> 0 Then
  MsgBox "No cells in Column K as constants"
End If
End Sub

@Fonzie:
I doubt your code is taking care of Column K being filled?

Ciao,
Holger

[fonzie29]

Thanks Ciao Holger!

Vimalanathk,
Try out this code.

Sub test()
Dim row As Long
row = 2
Do
    If Cells(row, 1).Value <> "x" And Cells(row, 1).Value <> "" Then
        Cells(row, 12).Value = "=+RC[-11]"
    End If
    row = row + 1
Loop Until Cells(row, 3).Value = "Powered by GL Compliance Manager"
End Sub

Thanks,
Fonzie

[vimalanathk]

Your bottom code is giving me the output for blank line of status columns also where i need the out put only the status is updated... it should be blank for status blank line items.

Regards,
Vimala K

[fonzie29]

Hi,
Not sure exactly what you are trying to do but I'm assuming that you want to copy down a formula until you find the word "Powered by GL Compliance Manager" in another column assuming its column L.
Try this if you want the formula to be next to column K.

Sub test()
Dim row As Long
row = 2
Do
Cells(row, 12).value = "=+RC[-11]"
row = row + 1
Loop Until Cells(row, 11).value = "Powered by GL Compliance Manager"
End Sub

Thanks,
Fonzie

[fonzie29]

This should do it based on the file you provided.

Sub test()
Dim row As Long
row = 2
Do
Cells(row, 12).value = "=+RC[-11]"
row = row + 1
Loop Until Cells(row, 3).value = "Powered by GL Compliance Manager"
End Sub

Thanks,
Fonzie

[xlbiznes]

Hi,

Attached is my solution. Hope this helps.

[millz]

Just a side note on your original code, the code doesn't stop because you did not increment row variable so it's forever looking at row 2.