Solved: Assigning formula to a range of cells usign VBA

[itipu]

Hi all, ran into a couple of issues again...

I am trying to assign a formula to a Column B...

[vba]columnLength = objRecordSet.RecordCount
ActiveSheet.Range("B1:B$columnLength").Formula = "IF(ISERROR(VLOOKUP(A1,Sheet3!$A:$B,2,FALSE)),""0"", VLOOKUP(A1,Sheet3!$A:$B,2,FALSE))"[/vba]

I need to assign this formula to every cell in Column, and my last cell(row reference) should be equal to columnLength....

Also where in the formula there is A1 this should be incremented with every cell (down).. Excel does it automatically if I drag the formula down, but not sure how to do this programmatically using VBA...

Thanks a lot

Mike

[Bob Phillips]

[vba]

ActiveSheet.Range("B1:B$" & columnLength).Formula = _
"=IF(ISERROR(VLOOKUP(A1,Sheet3!$A:$B,2,FALSE)),""0"", VLOOKUP(A1,Sheet3!$A:$B,2,FALSE))"
[/vba]

[mikerickson]

R1C1 is designed for uses like this. The same string preserves relative/absolute refencing no matter where its placed.

With ActiveSheet
Application.Intersect(.Range("b:b"), .UsedRange).FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-1],Sheet3!C1:C2,2,FALSE)),""0"", VLOOKUP(RC[-1],Sheet3!C1:C2,2,FALSE))"
End With

[itipu]

This works great, now if a match occurs I get the value else I get a 0 put in the cell. Finally, if cell's value is equal to 0 I would like to remove the whole raw and shift everything up...

[VBA]Dim Rng As Range, row as Long
Set Rng = ActiveSheet.Range("B1:B$" & columnLength)
For row = 1 to $columnLength
If Rng.Item(row).Text = "0" Then
Rng.Item(row).EntireRow.Delete
End If
Next
[/VBA]

This however does not seem to work very well...

Thanks again!

[Bob Phillips]

You have to work backwqards, and Row is a VERY bad name for a variable

[vba]

Dim Rng As Range, iRow As Long
Set Rng = ActiveSheet.Range("B1:B$" & columnLength)
For iRow = columnLength To 1 Step -1
If Rng.Item(iRow).Text = "0" Then
Rows(iRow).Delete
End If
Next
[/vba]

[itipu]

Any idea if it is possible to simplify this in the initial vlookup formula, so that if value is not found instead if having a record with 0 it would just move to next record? Currently it takes more than 30min on 5200 records I mean jst this piece of code... for removing rows.

[mdmackillop]

[VBA] Application.ScreenUpdating = False
With Cells
.Columns(2).AutoFilter Field:=1, Criteria1:="0"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Columns(2).AutoFilter
End With
Application.ScreenUpdating = True

[/VBA]

[mikerickson]

The row deletion would be faster if there wasn't error handeling built into the VLOOKUP.

Application.ScreenUpdating = False
Dim restoreCalc as Long: restoreCalc=Application.Calculation
Application.Calculation = xlManual
With ActiveSheet

  Application.Intersect(.Range("b:b"), .UsedRange).FormulaR1C1 = _
          "=VLOOKUP(RC[-1],Sheet3!C1:C2,2,FALSE)"

  Calculate

  .Range("b:b").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete

End With
Application.Calculation= restoreCalc
Application.ScreenUpdating = True

Excel can find errors faster than it finds zeros.