Simple help needed with Excel

[ddrafts]

I pull data from a SQL database. I have the formula =IF(ISNA(VLOOKUP(B2,TaxRate!$F$2:$G$510,2,0)),"",VLOOKUP(B2,TaxRate!$F$2:$G$510,2,0))

It works great. What I would like to do is have it fill in down the column if their is data in B2. That way I do not have to copy it down. Is this possible

Thanks in advance
Doug

 

[wmichael]

Can you try rephrasing the question with a little more details?

Looks like you have a formulae on a worksheet that references values in another work sheet. Are you always going to use the vlookup on B2, or will it be B3, B4, etc? What do you mean by 'fill down the column' if there is data in B2?

Should be a quick answer once I can visualize better what you are asking.



~wmichael

"small change can often be found under seat cushions

 

[ddrafts]

It is alway going to be on B2.

I guess what I'm trying to say is I do not want to Copy and Paste the formula to every row in D. I would like it do Copy it to the next row in D if their is something in B2.

I hope that this helps.

Thanks
Doug

 

[Rofeu]

Hi,

if you pull the data through MS Query you can check the option 'Fill down formulas in columns adjacent to data' in the properties of the query.

Otherwise I think you'd have to use the Worksheet_Change event with something to the extend of:

With Cells(4, 1)
     .AutoFill Destination:=Range(.Address & ":" & Cells(65536, 2) _
        .End(xlUp).Offset(0, 2).Address)
End With

Cheers,

Roel

 

[wmichael]

You will have to program a macro, then. There are only three ways to populate a cell;

1) Manul entry
2) Formula
3) Automated macros
4) Copy and paste, or use fill handle (same idea, but quicker, though it will automatically increment the row references; b2 would become b3, b4, etc.)

A macro may be a little more than you wish to do. If, however, you are going ahead with a macro, the pseudocode for this would look something like...

for x = (start row) to (end row)
theRange = "D" & x (will be D2, D3, D4, etc. if your start row is '2')
if b2(<>""> then theRange.value = your formula
next x



~wmichael

"small change can often be found under seat cushions

 

[wmichael]

Rofeau, I was not under the impression there would be adjacent data, but your solution would be elegant if there is.

~wmichael

"small change can often be found under seat cushions

 

[SkipVought]

SOP not elegant. Related data OUGHT to be in adjacent columns to the QueryTable in order to work with Sort, Filter, Naming and other Table Features.

Skip,

_[red][/red]

 

[ddrafts]

Thank you for all of your help. Here is what I came up with today

Sub Price1()

    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-12],TaxRate!R2C6:R567C7,2,0)),"""",VLOOKUP(RC[-12],TaxRate!R2C6:R567C7,2,0))"
    Range(Range("N3"), Range("M65536").End(xlUp).Offset(0, 1)).Select
    Selection.FillDown
    
End Sub

 

[ddrafts]

Now I do not understand. When I try to use it in the next colume with a different formula it does not work. It puts the formula as '='L2''*''N2'

Here is what I'm using for it.

    ActiveCell.FormulaR1C1 = _
        "=L2*N2"
    Range(Range("O3"), Range("N65536").End(xlUp).Offset(0, 1)).Select
    Selection.FillDown