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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.