Code to Update if Positive

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91
Is it possible to update a column of IF statements to the "number" where the IF statement returns a positive value, otherwise leave it as an IF statement (probably via VBA code)?

This is an abridged version of the IF statement “=IF(C33=$K$9,$K$10,0)”.

Thanks,
SKK
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,179
Office Version
  1. 365
Platform
  1. Windows
How about, something like
Code:
Sub EssKayKay()
   Dim Cl As Range
   
   For Each Cl In Range("L2", Range("L" & Rows.count).End(xlUp))
      If Cl > 0 Then Cl.Value = Cl.Value
   Next Cl
End Sub
 

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91
Thanks Fluff for your response. Please bear with me a bit but what am I to do with this code? I assume I have to define the range but where does that go?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,179
Office Version
  1. 365
Platform
  1. Windows
What range do you want it to work on?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,179
Office Version
  1. 365
Platform
  1. Windows
In that case, try
Code:
Sub EssKayKay()
   Dim Cl As Range
   
   For Each Cl In Range("I33:I1500")
      If Cl > 0 Then Cl.Value = Cl.Value
   Next Cl
End Sub
 

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91
In that case, try
Code:
Sub EssKayKay()
   Dim Cl As Range
   
   For Each Cl In Range("I33:I1500")
      If Cl > 0 Then Cl.Value = Cl.Value
   Next Cl
End Sub

Excellent that appears to be doing it. I added some code to run your code if the value in a specific cell is updated. It also appears to be working. Here's what I added.

'====================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$10" Then
EssKayKay
End If
End Sub
'=====================================

Again, thank you very much Fluff....
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,179
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,787
Messages
5,524,875
Members
409,608
Latest member
GigaPat

This Week's Hot Topics

Top