While + If Statements in VBA

[gelen4o]

Hi,

I have a worksheet full of data for which I would like to create serial numbers, given certain critria are met.

I'm using the "While" function to loop through rows, for as long as they have some text. Then I'm utilizing an "If Statement" to see if 3 criteria are met.

if so, I would like VBA to create a serial number by concatenating specific fields and text; otherwise just carry on with next row.

I get an error message "Object Required" but have no idea what it refers to

Sub SerialNumber()
Dim Statement As Worksheet
Dim i As Long
' i = row number
i = 3
Set Statement = Activebook.Sheets("Statement")
  While WorksheetFunction.IsText(Sheets("Statement").Cells(i, 1)) = True
    With Statement
          If (Range(i, 1) = "T" And Range(i, 90) = "E" And Range(i, 91) = "E") Then
             
              .Cells(i, 92).Value = WorksheetFunction.CONCATENATE(Range(i, 74), "N", Range(i, 20))
            
          Else:
              
          End If
          
    End With
    
    
    i = i + 1
Wend
                 
End Sub

 

[Joe4]

"Activebook" is not valid. It should be "ActiveWorkbook".

 

[gelen4o]

"Activebook" is not valid. It should be "ActiveWorkbook".

Sadly there is more to it. Now I get a message that my Range method has failed

 

[Fluff]

Hia try

    With Statement
          If cells(i, 1) = "T" And cells(i, 90) = "E" And cells(i, 91) = "E" Then
             
              .cells(i, 92).Value = WorksheetFunction.CONCATENATE(cells(i, 74), "N", cells(i, 20))
            
              
          End If
          
    End With

 

[gelen4o]

Hia try

    With Statement
          If cells(i, 1) = "T" And cells(i, 90) = "E" And cells(i, 91) = "E" Then
             
              .cells(i, 92).Value = WorksheetFunction.CONCATENATE(cells(i, 74), "N", cells(i, 20))
            
              
          End If
          
    End With

I get an error message that states "Object Doesn't supoort this property or method. I tried adjusting the code as follows but with no luck

Set Statement = Worksheets("Statement")
  While WorksheetFunction.IsText(Sheets("Statement").Cells(i, "L")) = True
  
        
          If Statement.Cells(i, "L") = "T" And Statement.Cells(i, "A") = "E" And Statement.Cells(i, "B") = "E" Then
             
              Statement.Cells(i, "C").value = WorksheetFunction.CONCATENATE(Statement.Cells(i, "CG"), "N", Statement.Cells(i, "AE"))
            
              
          End If
          
    
    
    i = i + 1
    
  Wend

 

[Fluff]

Try

Sub SerialNumber()
Dim Statement As Worksheet
Dim i As Long
' i = row number
i = 3
Set Statement = ActiveWorkbook.Sheets("Statement")
With Statement
    While WorksheetFunction.IsText(.cells(i, 1)) = True
        If .cells(i, 1).Value = "T" And .cells(i, 90).Value = "E" And .cells(i, 91).Value = "E" Then
            .cells(i, 92).Value = .cells(i, 74).Value & "N" & .cells(i, 20).Value
        End If
        
        i = i + 1
    Wend
End With
       
End Sub

 

[gelen4o]

Try

Sub SerialNumber()
Dim Statement As Worksheet
Dim i As Long
' i = row number
i = 3
Set Statement = ActiveWorkbook.Sheets("Statement")
With Statement
    While WorksheetFunction.IsText(.cells(i, 1)) = True
        If .cells(i, 1).Value = "T" And .cells(i, 90).Value = "E" And .cells(i, 91).Value = "E" Then
            .cells(i, 92).Value = .cells(i, 74).Value & "N" & .cells(i, 20).Value
        End If
        
        i = i + 1
    Wend
End With
       
End Sub

You, Sir, are amazing !

 

[Fluff]

Glad to help & thanks for the feedback