VBA For Loop - Adding certain input to a cell if row contains characters?

Question

EDIT: Here I have updated Code that @k1dfr0std was kind enough to help me with:

Dim lrow As Long
Dim lLastRow As Long
'This is where the error is happening
lLastRow = ThisWorkbook.Range(Cells(Rows.Count, "A").End(xlUp).Row)
Dim i As Long 'Using Long in case there are 1000's of Rows being used. . .
'If there are 1000's and 1000's of rows, you may want to reference

For lrow = 19 To lLastRow
    'Using "With" statement here to prevent the need to retype all of the jargon
    'at the beginning of each line ("ActiveSheet.Range(Cells(lRow,1)).
    With ActiveSheet.Range(Cells(lrow, 1))
    
    'Using the "Like" operator here allows us to use Wildcards in our search Vs the "Instr" structure.
    If .Value Like "*CON*" Then
        'Range does not have to be set, nor does it have to be selected.
        'The Range is actually defined by the "With" statement, so we only need to specify the .Value property
        '.Offset will always modify the offset of the range specified.
        'We are using the lRow determined by the loop, and Column "A" referenced as "1"
        'The offset is the same Row "0" and 2 columns to the left (Assuming you want to SKIP Column B since you always delete it.
        .Offset(0, 8).Value = i
        'Incrementing i ONLY when "CON" is found
        i = 22
    Else
        .Offset(0, 2).Value = "17"
    
    End If

Next lrow

End Sub

I am now getting this error: Compile error:

Method or data member not found and "Range" is highlighted in row 3. I am not sure how to tackle this.

I am working with VBA and trying to add to a for loop code macro I have. The code I have deletes a column, has a for loop inserting a row if a cell is bold (this is what I would like to add to, or add another loop), and then adds a specific descriptor. Here is my code:

Range("B:B").Delete

Dim lRow As Long
Dim lLastRow As Long
lLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For lRow = lLastRow To 19 Step -1
    If Cells(lRow, "A").Font.Bold = True Then
        Cells(lRow, "A").EntireRow.Insert     
    End If
Next lRow

Rows("19:19").Select
Selection.Insert Shift:=xlDown
Range("B20").Select
ActiveCell.FormulaR1C1 = "Configuration Name"
Range("C20").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[1]C[-1]"
Range("D20").Select

My hope is to have the loop go through the same column as the bold, but if matches certain parameters, then it will add a value to a cell in its respective row.

For example, if a column contains 'apple', I want to direct it to another column and have it show 'orange'.

Answer 1

After more conversation, I think what you are after is as follows.

As @Pᴇʜ mentioned, it's beneficial to get out of the habit of using .Select for everything you do - it's messy, unnecessary and can cause more grief in the long run. . . Unfortunately this often given as artifacts from recording a macro in Excel.

If you do not want to restrict things to a single sheet, but rather, you want to include all sheets and each sheet has the same condition you are looking for, then the modified code below should work for your needs.

In addition, if you do not perform any calculations to "i" you could set i to equal whatever you want outside of the for loop. I also noticed you are changing the value 8 columns over if the value you need is found, and 2 columns over if it is not found - I'll incorporate this into my code.

  Sub loopinThruRows_for_CON()
    Dim lrow as Long
    Dim lLastRow As Long
    'Fixed the line below here - forgive me - '.Range' has to be qualified by a sheet.
    lLastRow = Thisworkbook.Sheets("YourSheetName").Range(Cells(Rows.Count, "A").End(xlUp).Row)
    
    'Leaving this line in for posterity
    'Dim i as Long 'Using Long in case there are 1000's of Rows being used. . .
    
    'The following line will create variable "i" as an integer and assign the value of 22.
    Const i as integer = 22
    Dim j as Integer 'Using integer for now assuming you have only a hundred sheets or so
    'If there are 1000's and 1000's of rows, you may want to reference the link
      'I provided below. . .
    
    For j = 1 to ThisWorkbook.Sheets().Count
      'This line will activate the 1st sheet, then go through however
      'many sheets you have in the whole workbook.
      'If you need to filter a sheet so it does -NOT- get processed,
      'use an If/Then struct to check the appropriate name.
      'This prevents the "ActiveSheet" portion from doing the work on the
      'Sheet over and over again.
      ThisWorkbook.Sheets(j).Activate

      For lRow = 19 To lLastRow
          'Using "With" statement here to prevent the need to retype all of the jargon 
          'at the beginning of each line ("ActiveSheet.Range(Cells(lRow,1)).
          With ActiveSheet.Range(Cells(lRow,1))
        
          'Using the "Like" operator here allows us to use Wildcards in our search Vs the "Instr" structure.
          If .Value Like "*CON*" Then
              'We are using the lRow determined by the loop, and Column "A" referenced as "1"
              'The offset is the same Row "0" and 8 columns to the right 
              'Use of negative numbers will go the opposite directions.
              .Offset(0,8).Value = i
          
          Else
              .Offset(0,2).Value = "-"
        
          End If
    
      Next lRow

    Next j
    
  End Sub

As an aside - if you are dealing with 1000's of rows, it may be more beneficial to put the data into an Array instead of iterating through the 1000's of rows, as this can be much faster than trying to manipulate the data inside the cells first. Reference this StackOverflow article.