code not looping.

[orsm6]

hi all - i thought this code would loop through the range and put a formula in every box ... but it doesn't. how can it be changed to make it loop?

    With Range("E2:E100").Find("Z004").Offset(0, 10)
        .Value = "=((K3*M3)/N3)"
    End With

if it is possible, can the code just loo for Z00 rather than Z004.... i could use that to fill the rest of the column rather than have separate codes for each....

thank you

 

[orsm6]

just realised a loop wont help. The formula put into the cell will be same in every row.... i need it to look at each row as it fills each cell.... hmmmm

 

[snjpverma]

you can use VBA to enter the formula as well.
instead of .Value you can use .formula
You haven't clearly specified in which range you want the formulas to be entered, so we can't suggest a solution without knowing that.

 

[orsm6]

you can use VBA to enter the formula as well.
instead of .Value you can use .formula
You haven't clearly specified in which range you want the formulas to be entered, so we can't suggest a solution without knowing that.

thanks for your thoughts. the range is dynamic and across multiple columns. but to get something working now the formula would be in column O
how does the .formula syntax work?

edit: the formula always starts in cell O3 but could vary anywhere form O4 to O100

 

[snjpverma]

what determines the last row number?

Does the word "Z00" in column E determines the last row number until which the formula should be entered? And what should happen if there are multiple instances of "Z00" in Column E?

 

[footoo]

thanks for your thoughts. the range is dynamic and across multiple columns. but to get something working now the formula would be in column O
how does the .formula syntax work?

edit: the formula always starts in cell O3 but could vary anywhere form O4 to O100

It's not clear what you want to do.
Do you mean that if any cell in column E contains "Z00" then put the formula in the same row in column O ?

 

[orsm6]

what determines the last row number?

Does the word "Z00" in column E determines the last row number until which the formula should be entered? And what should happen if there are multiple instances of "Z00" in Column E?

yeah in column E there is a combination of Z004, Z003, Z002 any of those number could be the last row. there is a blank line after this then more data. the formula in Column O still looks at the same columns, just each row the formula is in regardless if it is Z004, Z003 or Z002.... Column E as i said ranges in how many rows there are form report to report.

 

[snjpverma]

Try if this is what you are looking for:
The below code searches for the last row in Column E for any cell that contains "Z00" and consider it as last row and then paste the formula in Column 0.

Sub enterformula()
    
Dim x As Long
            
    x = Range("E2:E100").Find(WHAT:="Z00", LookAt:=xlPart, SearchDirection:=xlPrevious).Row
    
    Range("O2:O" & x).Formula = "=((K3*M3)/N3)"
    
End Sub

 

[snjpverma]

I think your Column E2:E100 is not a fixed range and could go below that.
In that case, Try this

Sub enterformula()

Dim x As Long
Dim lr As Long

lr = Range("E" & Rows.Count).End(xlUp).Row

    x = Range("E2:E" & lr).Find(WHAT:="Z00", LookAt:=xlPart, SearchDirection:=xlPrevious).Row
   
    Range("O2:O" & x).Formula = "=((K3*M3)/N3)"
   
    End Sub

 

[orsm6]

I think your Column E2:E100 is not a fixed range and could go below that.
In that case, Try this

Sub enterformula()

Dim x As Long
Dim lr As Long

lr = Range("E" & Rows.Count).End(xlUp).Row

    x = Range("E2:E" & lr).Find(WHAT:="Z00", LookAt:=xlPart, SearchDirection:=xlPrevious).Row
  
    Range("O2:O" & x).Formula = "=((K3*M3)/N3)"
  
    End Sub

the second code is definitely almost there... thank you... it's just one row out. the formula starts in E3 in all cases.....