1

I have tried several methods to eliminate my VBA error but it does not help. During the first run, there will be error code 9. Upon running again, the code works fine. I have another code with no error but does not execute the VBA code correctly the first run. Upon running the code again, it execute the VBA code correctly.

I have tried using F8 to debug the problem, i have tried looping.

Sub TXNFont()

Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim lastColumn As Integer
    Dim rng As Range
    Set rng = ActiveSheet.Cells

    Sheets("TXN Speed").Select

    lastColumn = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
    End With

    Sheets("TXN Speed").Select
    Cells.Select
       With ActiveSheet
        .Select
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
          For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow, lastColumn)
                If Not IsError(.Value) Then
                    If .Value > 3500 Then .EntireRow.Font.Color = RGB(255, 0, 0)
              End If
            End With
        Next Lrow
    End With
    With Application
             .Calculation = CalcMode
    End With

    Sheets("TXN Speed").Select
    With ActiveSheet
        .Select
        Range("A1:A5").EntireRow.Font.Color = RGB(0, 0, 0)
        End With

End Sub

Sub NetFont()

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim lastColumn As Integer
    Dim rng As Range
    Set rng = ActiveSheet.Cells

    With Sheets("Network").Select

    Cells.Select
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With

    lastColumn = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    Cells.Select


    Cells.Select
     With ActiveSheet
        .Select
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

          For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow, lastColumn)
                If Not IsError(.Value) Then
                    If .Value < 10 Then .EntireRow.Font.Color = RGB(255, 0, 0)
                End If
            End With
        Next Lrow
    End With

    With Application
             .Calculation = CalcMode
    End With

    Sheets("Network").Select
    With ActiveSheet
        .Select
        Range("A1:A5").EntireRow.Font.Color = RGB(0, 0, 0)
    End With
    End With

End Sub

Sub TXNFont() will have error during first run, but will execute the code with no issue if I hit run again.

Sub NetFont() will execute the coding with error during first run, but will execute the code correctly if I hit run again.

These codes were link to another VBA code which was running fine with no issue. I hope that the codes I made run smoothly with no error during their first run. Thanks in advance people!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jasper
  • 26
  • 1
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and it will probably solve your issue. Apply this technique to your code and update it in the question if you keep getting the errors. – Pᴇʜ Jan 18 '19 at 09:32
  • Thanks for correcting the error. I have tried to reduce .select and .activesheets but i am not able to get the code to run correctly. First worksheet contains all the data which will be used to form worksheet TXN Speed, Network and Error Code. Running all the VBAs together will not have any runtime error etc but the highlighted rows will be wrong. When i separate the VBAs (arrangement of worksheets and pivot tables, changing font colours) my font VBA must run twice to eliminate errors and wrong rows highlighted – Jasper Jan 22 '19 at 03:05
  • It's not about reducing them it's all about removing them completely. Code that uses `.Select` or `ActiveSheet` is known to be unstable. It might work once, while it might fail another time. Define your sheets/ranges as variables and address concrete sheets and ranges without using `.Select` or `ActiveSheet` they must be replaced completely. The technique is well explained in the link I gave you (further information can be found on Google). This should be your first concern otherwise you never can be sure that your code does what you expect it to do. – Pᴇʜ Jan 22 '19 at 07:18

0 Answers0