VBA Help - Row number not updated while copying formula

**rkambala** · 06-08-2011, 10:35 AM

Hi,

I am new to VBA excel. I am trying to copy the forumla from the previous row to the current row in a loop.

While doing so the formula gets copied but the row Number is not updated.i.e the row number in the Forumla of NEW Row still points to the previous row.

Kindly let me know how do I update the rownumber in the formula.

----
wks.Cells(LastRow, "I").Formula = wks.Cells((LastRow - 1), "I").Formula
-----

Any help/suggestions are highly appreciated.

Thanks,
Ravindra

**Domski** · 06-08-2011, 10:41 AM

When populating a range with a formula you don't normally need to increment the row number like that. Are you populating the initial cell's formula earlier on in you code?

Also please remember to use code tags when posting code to the forum per the forum rules.

Dom

**rkambala** · 06-08-2011, 10:53 AM

Hi,

Thanks for your reply.

I am using an excel where the forumla is already populated. I am just trying to add new rows and copy the formula from the previous rows.

loop start
wks.Cells(LastRow, "I").Formula = wks.Cells((LastRow - 1), "I").Formula

LastRow = wks.Cells.Find(what:="*", after:=wks.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
loop end

**Domski** · 06-08-2011, 11:00 AM

I think it would help to see a sample workbook with your full code showing what you are trying to achieve.

Dom

**rkambala** · 06-08-2011, 11:13 AM

Hi,

Attached is the sheet with sample copy.

In this attachment, I will copy a new ROW i.e no 7 with new data. I would like to copy the same formula from ROW-6 for columns I to Columns- T.

Hope this is clear.

Thanks,
Ravindra

**rkambala** · 06-08-2011, 11:16 AM

Hi,

Sorry, below is the code snippet. Here I am trying to copy formula for only One column J for testing.

   
    'Count the number of rows to be copied from the current week sheet
    Dim cntRowsToCopy
    cntRowsToCopy = Application.CountA(Range("A:A"))
    Debug.Print cntRowsToCopy
 
    Dim i
    For i = 1 To cntRowsToCopy Step 1
    wks.Cells(LastRow, "A").Value = ws.Cells(i, "A").Value
    wks.Cells(LastRow, "B").Value = ws.Cells(i, "B").Value
    wks.Cells(LastRow, "C").Value = ws.Cells(i, "C").Value
    wks.Cells(LastRow, "D").Value = ws.Cells(i, "D").Value
    wks.Cells(LastRow, "E").Value = ws.Cells(i, "E").Value
    wks.Cells(LastRow, "F").Value = ws.Cells(i, "F").Value
    wks.Cells(LastRow, "G").Value = ws.Cells(i, "G").Value
    wks.Cells(LastRow, "H").Value = ws.Cells(i, "H").Value
    wks.Cells(LastRow, "I").Formula = wks.Cells((LastRow - 1), "I").Formula
    LastRow = wks.Cells.Find(what:="*", after:=wks.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
    Next i
    'Find the last row with data for formatting purpose and adding Week Ending comment for identification.
    LastRow = wks.Cells.Find(what:="*", after:=wks.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Debug.Print LastRow

**Domski** · 06-08-2011, 11:32 AM

If you paste extra data into columns A:H and then run this:

Sub Test()

Dim lngDataLastRow As Long
Dim lngFormulaLastRow As Long

lngDataLastRow = Cells(Rows.Count, 1).End(xlUp).Row
lngFormulaLastRow = Cells(Rows.Count, 9).End(xlUp).Row

Range("I" & lngFormulaLastRow).Resize(1, 12).AutoFill Destination:=Range("I" & lngFormulaLastRow & ":T" & lngDataLastRow), Type:=xlFillDefault
 
End Sub

It will then populate columns I:T by copying the formula down.

Dom