CODEWITHSUNDEEP
excelvba
Meru
Meru

Reputation: 1

VBA script: Code Runs fine in Debug mode but not in full run mode

VBA script: Code Runs fine in Debug mode but not in full run mode. Code is to read each cell in Col I, check for 1st pos IF numeric and then write the Pos of the same in Col J ?

The code runs fine in debug mode but not in full run.

CODE writteN

Dim CellRef As String
Dim StringLength As Integer
j = 1
i = 1

For j = 2 To LastRow1
   StringLength = Len(Cells(j, "I"))
   i = 1
   For i = 1 To StringLength
      If IsNumeric(Mid((Cells(j, "I")), i, 1)) Then
         Cells(j, "J") = i
         i = StringLength + 1
      End If
   Next i
Next j

Upvotes: 0

Views: 75

Answers (1)

Damian
Damian

Reputation: 5174

I'm not sure if you have variables declared before or not. To avoid the that come by not declaring them, I recommend you using Option Explicitat the top of your code.

On the other hand, I guess your problem comes from the ActiveSheet at the moment you are running your macro, since you didn't full qualify your ranges, they go to the ActiveSheet to avoid this you can read the comments:

Option Explicit
Sub TesT()

    Dim CellRef As String 'not sure if you are using it later
    Dim StringLength As Long ' you used Integer, Byte or Long, Integer is a Long cutted.
    Dim MyRange As Range 'we will set here the working range
    Dim C As Range 'It is always better to loop through cells with For Each
    Dim LastRow1 As Long 'Not sure if you declared it before
    Dim i As Long 'Not sure if you declared it before

    With ThisWorkbook.Sheets("SeetName") 'always full qualify your ranges: workbook-worksheet-range
        LastRow1 = .Cells(.Rows.Count, "I").End(xlUp).Row 'last row on column I
        Set MyRange = .Range("I2:I" & LastRow1) 'this will be your working range
        For Each C In MyRange 'this will loop through all the cells between J2 to J LastRow1
            StringLength = Len(C)
            For i = 1 To StringLength
                If IsNumeric(Mid((C), i, 1)) Then
                   .Cells(j, "J") = i 'full qualify the range, note the "." before Cells, referencing to the with line
                   Exit For 'this will exit the loop so only the first match remains.
                   ' i = StringLength + 1 'don't know why did you do this, but if you intend to keep the first position the above line is it.
                End If
            Next i
        Next C
    End With

End Sub

Upvotes: 1

Related Questions