Can a value be assigned to a cell based on condition without using a l

[makeitwork09]

I am using Excel 2007.

Is there a way to modify this code so that a loop is not required? Would I need to use an array? My goal is to make the code faster.

   For Each FUIntPayoffCellRng In Range(Range("AG2"), Range("AG2").End(xlDown))
      If FUIntPayoffCellRng.Value <> 0 Then
            Cells(FUIntPayoffCellRng.Row, "I").Value = FUIntPayoffCellRng.Value
      Else
            Cells(FUIntPayoffCellRng.Row, "I").Value = Cells (FUIntPayoffCellRng.Row, "I").Value
      End If
   Next FUIntPayoffCellRng

I attempted to do this, but it, of course, does not work

   If Range("AG2:AG" & FULastRow).Value <> 0 Then
       Range("I2:I" & FULastRow).Value = Range("AG2:AG" & FULastRow).Value
   Else
       Range("I2:I" & FULastRow).Value = Range("I2:I" & FULastRow).Value
   End If

Thanks

 

[SkipVought]

hi,

Please explain what you are trying to accomplish WITHOUT using code-ese. What is your LOGIC? Your posted code means next to nothing!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Hi Skip,

We have a spreadsheet where if a value not equal to zero is in column AG we want to replace the value in the corresponding row of column I, but if the value in the cell in column AG is 0 we want to keep the value in the corresponding row of column I.

Thanks

 

[SkipVought]

Why not SIMPLY use a spreadsheet formula???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Lord have mercy. It is that simple.

I hate when I get so focused on something, I end up overlooking the simple stuff.

Thanks Skip

 

[Gavona]

You certainly don't need to loop:

myRange.Replace What:="0", Replacement:="My text", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

If it was more complex then I would suggest Autofilter then
MyRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "Test"

Regards,

Gavin

 

[makeitwork09]

Thanks Gavona,

Do you have a suggestion on how to use the range object, replace method if the replacement value is from another cell and the value is a number?

 

[makeitwork09]

I got Skips suggestion to work, but Gavona based on what is required, I do not understand how the replace method would work without a loop.

Here is what is required. If AG2 is 0, than I2 stays as the number it currently is. If AG2 is 100,000, for example, then I2 should be replaced with AG2.

This test has be done for each cell in column AG. Where the cell in AG is not equal to 0, the corresponding cell in column I needs to be changed to the value from column AG. Note too that cell value in AG can be 0 or any number (currency amount) not equal to zero.

 

[makeitwork09]

I got Skip's suggestion to work, but I've been spending time trying to learn so I also found the following code that I can use. It does use a loop, however it is much more efficient than what I originally had.

   'uses and looks in PIF column; where 0 changes to prin
   For Each PiF In Range("AG2:AG" & Cells(Rows.Count, 8).End(xlUp).Row)
       If PiF = 0 Then PiF = Cells(PiF.Row, 3)
   Next PiF

 

[makeitwork09]

The indexes should be 33 and 9, not 8 and 3. The latter were from when I was testing the code. I forgot to replace them.

 

[Gavona]

Try this approach.
Set xl to accept R1C1 addressing
replace zeros with a formula using relative addressing
convert formulaes to values
reset xl to use a1 style addressing
All formulae in myRange are converted to values - this may or may not be an issue for you.

Sub Macro1()
Dim myRange As Range
Set myRange = Selection
Application.ReferenceStyle = xlR1C1
With myRange
    .Replace What:="0", Replacement:="=RC[3]", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    .Value = Selection.Value
End With
Application.ReferenceStyle = xlA1
End Sub

Not sure about relative speed but you could also use this approach, assuming that there are no blank cells in the column that you wish to remain blank. This approach will have the advantage of not converting formulae to values other than the target cells:

Sub Macro2()

Dim myRange As Range
Set myRange = Selection
'Application.ReferenceStyle = xlR1C1

With myRange
    .Replace What:="0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[3]"
    .Value = Selection.Value
End With
End Sub

Gavin