PDA

View Full Version : Solved: How to use replace function in VBA?



genracela
07-08-2010, 05:05 PM
I want to replace cells from column F to S with zero(0), with a condition IF cells are >0.

how do you use replace function with that condition?

I tried doing:
Sub f()
ActiveSheet.UsedRange.Replace >0, "", xlWhole
End Sub

But it just reds out.

Thanks!

YasserKhalil
07-08-2010, 06:21 PM
Could you explain a little ?I can't understand what you want
Do you want to write a macro that do the same of a formula or what??
elaborate plz

mbarron
07-08-2010, 06:25 PM
You can't conditionally replace values using Replace

The following will replace all numbers greater than Zero in columns F through S

Sub test()
Dim rA As Range, rB As Range
Set rA = Range("F:S").SpecialCells(xlCellTypeConstants, 1)
For Each rB In rA
If rB > 0 Then rB = 0
Next
End Sub

genracela
07-08-2010, 06:26 PM
My condition is:

If cell is >0 replace it with 0

I have a previous project where I used:


Sub f()
ActiveSheet.UsedRange.Replace 0, "", xlWhole
End Sub


It worked, but this is to replace zero with blanks.

Now, what I want is if the condition 'IF cell is > 0' it will be replaced by "0".

I tried:


Sub f()
ActiveSheet.UsedRange.Replace > 0, "", xlWhole
End Sub


But it gives me an error(red highlight)

genracela
07-08-2010, 06:34 PM
Thanks MBarron! This is perfect!

Mwahhh! Mwahhh! Mwahhh!