how to stop this loop....

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

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

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

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