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]

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

i missed the line o2.. changed to O3.. been looking at this way too long today!

 

[snjpverma]

Glad you got it working. Thanks for the feedback.

 

[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

if i wanted to aim the code specifically at Z003 can i just change Z00 to Z003?

 

[snjpverma]

if i wanted to aim the code specifically at Z003 can i just change Z00 to Z003?

yes, absolutely.

 

[orsm6]

yes, absolutely.

if i change it to Z003 it fills the rows in column E that have Z004 in them...... but excludes any line with Z002
if i put Z002 in the code it fills all rows that have Z00 in it.

 

[snjpverma]

Shouldn't happen like that if you have given proper references.

However, you can try changing this line :
x = Range("E2:E" & lr).Find(WHAT:="Z00", LookAt:=xlPart, SearchDirection:=xlPrevious).Row

To this line
x = Range("E2:E" & lr).Find(WHAT:="Z00", LookAt:=xlwhole, SearchDirection:=xlPrevious).Row

After making changes in the code if problem persists, just paste your entire code here so that the members can have a look at it.

 

[orsm6]

Shouldn't happen like that if you have given proper references.

However, you can try changing this line :
x = Range("E2:E" & lr).Find(WHAT:="Z00", LookAt:=xlPart, SearchDirection:=xlPrevious).Row

To this line
x = Range("E2:E" & lr).Find(WHAT:="Z00", LookAt:=xlwhole, SearchDirection:=xlPrevious).Row

After making changes in the code if problem persists, just paste your entire code here so that the members can have a look at it.

here is the code. I have tried to point it at the word CONV which is also in column E. when i run the code it fills all of column O rather than only the lines with CONV in it.

Sub convformula()

Dim x As Long
Dim lr As Long
Worksheets("Layout").Activate
lr = Range("E" & rows.Count).End(xlUp).Row

    x = Range("E3:E" & lr).Find(WHAT:="CONV", LookAt:=xlWhole, SearchDirection:=xlPrevious).Row
   
    Range("o3:o" & x).Formula = "=K3*M3/N3"
   
End Sub

thanks heaps for your help so far

 

[snjpverma]

here is the code. I have tried to point it at the word CONV which is also in column E. when i run the code it fills all of column O rather than only the lines with CONV in it.

Is CONV the entire cell value or is it a part of the cell like "Conventional" or "Conviction".
Also, until now I thought you have to put formula in the Entire column O. I will try to change my code.

 

[orsm6]

Is CONV the entire cell value or is it a part of the cell like "Conventional" or "Conviction".
Also, until now I thought you have to put formula in the Entire column O. I will try to change my code.

CONV is the text.

nope, only need the formula in the row that has the word CONV in it or Z004 etc etc.

 

[snjpverma]

Sub enterformula()

Dim x As Long
Dim lr As Long
Dim c As Range

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

    For Each c In Range("E3:E" & lr)
        If c.Value = "CONV" Then
            c.Offset(0, 10).Formula = "=((RC[-4]*RC[-2])/RC[-1])"
        End If
    Next c
    
End Sub