If statement within a loop

[Legacy 93538]

Hi

Does anyone know how to create an if statement which has two variables?

I need to create an if statement which will go inside a loop that loops through a cell range which first checks if it is empty and if its not then i need to to say if the value in the cell on row 1 of the corresponding column = "12 months" and the value in the cell in column E of the corresponding row contains "Per Unit then place "12 month per unit" into the cell in sheet variable PPCWBSht in the 4th column.

I had a go at the writing the code myself however i get an error saying "Subscript out of range" and it highlights the row i have coloured red in the code below.

NRow = 2
Application.StatusBar = "Loop through calcs Form and populate calculations table"
For Each cell In PPFWB.Sheets("IRFORM").Range("F4:U640")
    If cell.Value <> "" Then
        If  PPFWB.Sheets("IRFORM").Range(Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", cell.Column, 1) & "1").Value = "12  Mths" And Trim(PPFWB.Sheets("PPIIIFORM").Range("e" & cell.Row).Text) Like "*Per Unit" Then
            PPCWBSht.Cells(NRow, 4).Value = "12 month per unit"
        End If
        Application.StatusBar = cell.Address
    End If
Next cell

Does anyone know how to do this?

Thanks

Jessicaseymour

 

[cgcamal]

Hi Jessica,

You could try to assign first those long formulas to a variable to test and evaluate if contain the data
you expect before to pass through the If statement, after that the If statement will become easiest
to read and to fix if is needed, something like:

NRow = 2
Application.StatusBar = "Loop through calcs Form and populate calculations table"
For Each cell In PPFWB.Sheets("IRFORM").Range("F4:U640")
    If cell.Value <> "" Then

    [COLOR=Blue]W = PPFWB.Sheets("IRFORM").Range(Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", cell.Column, 1) & "1").Value
    Z = Trim(PPFWB.Sheets("PPIIIFORM").Range("e" & cell.Row).Text)[/COLOR]
    
        If [COLOR=Blue]W [/COLOR]= "12  Mths" And [COLOR=Blue]Z[/COLOR] Like "*Per Unit" Then
            PPCWBSht.Cells(NRow, 4).Value = "12 month per unit"
        End If
        Application.StatusBar = cell.Address
    End If
Next cell

 

[Legacy 93538]

Hi

Thanks for replying!

Ok i never thought of doing it that way! Thanks works perfectly