fill down macro

[Chenboy2]

I'm trying to create a fill down macro for the range D4:D5028

Here's the code I have so far:

I got most of this online.

Thanks.


Sub FixParent()
'
' FixParent Macro
'


'
Dim lastrow As Long


lastrow = Range("D4:D5028").End(xlUp).Row
'ActiveCell.FormulaR1C1


' Selection.AutoFill Destination:=Range("Table1[Parent]")
Selection.AutoFill Destination:=Range("D4:D5028" & lastrow)
Range("D4:D5028").Select
'Range("D4").Select
'Selection.End(xlDown).Select
'ActiveCell.Offset(0, 1).Select
'Range(Selection, Selection.End(xlUp)).Select
'Selection.FillDown


End Sub

 

[Dryver14]

thats quite a mess although only 2 lines of code are actually working, what are you trying to do, put the formula in D4 to D5028?

What is the formula?

 

[Chenboy2]

Yeah, that's the only cells I want to fill down. It's a vlookup formula that keeps getting messed up. I'm entering data in the table and it doesn't reference the right corresponding cells next to it. If you need the formula I'm using, I'll paste it.

The code doesn't really work.

 

[Dryver14]

yes post the formula and I am sure either myself or someone else will be glad to help,

Is the vlookup, lookiing at the cell to the left of it or something?

 

[Chenboy2]

=VLOOKUP(Actual!$C151,$CW$4:$CX$9,2)


CW CX
[TABLE="width: 228"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Expense[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Other Income[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Refund[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Reimbursement[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Savings[/TD]
[/TR]
</tbody>[/TABLE]

I hope this explains it.

 

[Dryver14]

sort of but what do you want to change in the formula as it goes down 5024 rows?

 

[Chenboy2]

That the value in CW matches with CX. What happens is that the value in CX gets shifted around. The only solution I found was to fill down in D4 down to D5028. If I could automate it with a macro, it would save time.

 

[Dryver14]

something like this.

Sub addit()


    Range("D5:D5028") = Range("D4").Formula
    
End Sub

 

[Chenboy2]

I'm confused, do I add that code to the existing code I have or do I create a new macro? How does that line up the values of CW and CX in columns C and D?

Let me explain, I enter a number from 1 through 6 in column C and the corresponding value (Income, Expense, etc.) is populated in D. For some reason the same row in C does not match in row D so the cell reference does not match and the information is wrong, so D5 will reference the value of C3 in the vlookup formula or any other cell, not C5.

 

[Chenboy2]

Let me explain a little more about what s going on:

It should be like this:

[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]Expenses[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2[/TD]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]Refund[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]Expense[/TD]
[/TR]
</tbody>[/TABLE]










But for some reason, it sometimes becomes like this:

[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]Refund[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]Income[/TD]
[/TR]
</tbody>[/TABLE]










The corresponding row in C doesn't match with row D. A fill down of the VLOOKUP formula will quickly fix the problem, but I want to write a macro so I can quickly do that.

Does that make sense?