PDA

View Full Version : Solved: Assigning formula to a range of cells usign VBA



itipu
06-17-2007, 02:07 AM
Hi all, ran into a couple of issues again...

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

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))"

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
06-17-2007, 02:14 AM
ActiveSheet.Range("B1:B$" & columnLength).Formula = _
"=IF(ISERROR(VLOOKUP(A1,Sheet3!$A:$B,2,FALSE)),""0"", VLOOKUP(A1,Sheet3!$A:$B,2,FALSE))"

mikerickson
06-17-2007, 02:19 AM
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
06-17-2007, 03:30 AM
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...

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


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

Thanks again!

Bob Phillips
06-17-2007, 04:14 AM
You have to work backwqards, and Row is a VERY bad name for a variable



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

itipu
06-17-2007, 04:32 AM
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
06-17-2007, 06:04 AM
Application.ScreenUpdating = False
With Cells
.Columns(2).AutoFilter Field:=1, Criteria1:="0"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Columns(2).AutoFilter
End With
Application.ScreenUpdating = True

mikerickson
06-17-2007, 08:13 AM
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.