code not looping.

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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?

Code:
    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 :)
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
Office Version
2016
Platform
Windows
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

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,936
Office Version
2016
Platform
Windows
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

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
Office Version
2016
Platform
Windows
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.

VBA Code:
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

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
Office Version
2016
Platform
Windows
I think your Column E2:E100 is not a fixed range and could go below that.
In that case, Try this

VBA Code:
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

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
I think your Column E2:E100 is not a fixed range and could go below that.
In that case, Try this

VBA Code:
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.....
 

Watch MrExcel Video

Forum statistics

Threads
1,095,267
Messages
5,443,428
Members
405,235
Latest member
1Thess521

This Week's Hot Topics

Top