[SOLVED] Fill across and down

[b50]

Hello,

See attached spreadsheet for example.

I can copy all of the data in D3 down for all for all cells that have data in column C with the following code:

    Range("D3").Select
    Selection.AutoFill Destination:=Range("D2:D" & Range("C65536").End(xlUp).Row)

However, my problem is that I can't figure out how to copy it across to the cells. I need it to look at row 2 and if it has data then input the formula in the respective row 3 and fill it down based on column c.

Let me know if that doesn't make sense.

Thank you for anyone who can help.


example.xls

[StephenR]

Not sure I understand, don't you mean D2, not D3? However, try this:

Sub x()

Dim c As Long

For c = 4 To Cells(2, Columns.Count).End(xlToLeft).Column
    Cells(2, c).AutoFill Destination:=Range(Cells(2, c), Cells(Cells(Rows.Count, "C").End(xlUp).Row, c))
Next c

End Sub

[b50]

That works great except that I have a vlookup formula in column D3. I guess I forgot to put that in my example. So, there is a formula there, that I need to do exactly what your code does above, but with the vlookup that is in column d.

[StephenR]

Now you've lost me. Update your workbook?

[b50]

Ok, the formula that i have in the spreadsheet doesn't work, but thats not a big deal. I have it working in my main sheet.

The vlookup formula that is in D3 needs to be filled down based on column b. It then needs to be filled over based on row 2, similar to what your first code did, but with the vlookup that is in D3.


Thank you, sorry for the confusion.

example2.xls

[StephenR]

If you're just trying to copy a formula from D3, perhaps this?

Range("D3").Copy Range("D3").Resize(Cells(Rows.Count, "B").End(xlUp).Row - 2, Cells(2, Columns.Count).End(xlToLeft).Column - 3)

[b50]

works perfectly. Thank you