Use formula in cell loop

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,311
Office Version
  1. 2016
Platform
  1. Windows
How can I use a cell address in a formula within a range loop?

I've tried the below but it doesn't work;

Code:
For Each cell In Range("B3:B18")
If cell.Value <> 0 Then
cell.Offset(0, 1).Formula = "=IF(Cell="""","""",COUNTIF(DEPLOYMENTS!$S$2:$S10000,Cell))"
End If
Next
 
Last edited:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,208
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Untested.
Code:
Cell.Offset(0, 1).Formula = "=IF(" & Cell.Value = "" & ","""",COUNTIF(DEPLOYMENTS!$S$2:$S10000," & Cell.Value & "))"
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,311
Office Version
  1. 2016
Platform
  1. Windows
Thanks Joe - doesn't work I'm afraid, I get 'FALSE' as a result.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What is the working formula in C3 if B3 is populated and you typed it in manually?
 
Last edited:

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,311
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

This:

=IF(B3="","",COUNTIF(DEPLOYMENTS!$S$2:$S10007,B3))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
cell.Offset(0, 1).Formula = "=IF(" & cell.Address & "= """" ,"""",COUNTIF(pcode!$S$2:$S10000," & cell.Address & "))"
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,311
Office Version
  1. 2016
Platform
  1. Windows
Fluff,

Thanks, that works, though the 'pcode' part threw me slightly!

Thank you to all that replied.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,990
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top