Formula in my Macro??

[Tarqs]

Hi I could do with some help putting a formula in this line of my code.

Code is

.Cells(currentRow, "M").Value = .Cells(currentRow - 1, "M").Value

I need the code to copy the formula that is in the cell above and not the value? Unfortunately I can not hard code the formula as it could be subject to change.

Thanks for any help

Tarqs

 

[VoG]

Perhaps

.Cells(currentRow, "M").Formula = .Cells(currentRow - 1, "M").Formula

 

[Tarqs]

Hi VoG thanks for the reply. It returns the exact formula from the row above and not the formula for the current row?

Example

Row 1 is =I1*L1
Code returns in Row 2 =I1*L1 and not the required =I2*L2

 

[VoG]

Perhaps try

With .Cells(currentRow - 1, "M")
    .AutoFill Destination:=.Resize(2)
End With

 

[Tarqs]

VoG it returns a compile error

I have changed some other elements of the operation so that the row above no longer changes so hard coding is now possible. Question is how?

Below is the code i am trying to amend.

Sub SAPImport()
    Dim wsDest As Worksheet
    Dim wsSource As Worksheet
    Dim rngSource As Range
    Dim rng As Range
    Dim firstRow As Long
    Dim currentRow As Long
 
    Set wsDest = ActiveSheet
 
    If Not Application.Dialogs(xlDialogActivate).Show Then
        Exit Sub
    End If
 
    Application.ScreenUpdating = False
 
    Set wsSource = ActiveSheet
 
    With wsDest
        currentRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        firstRow = currentRow - 1
    End With
 
    With wsSource
        If Len(.Range("A29").Value) > 0 Then
            Set rngSource = .Range("A28", .Range("A28").End(xlDown))
        Else
            Set rngSource = .Range("A28")
        End If
    End With
 
    For Each rng In rngSource.Cells
         With wsDest
            .Cells(currentRow, "A").Value = .Cells(currentRow - 1, "A").Value + 0.0001
            'column M will need to contain =I*L for the current row
[COLOR=red].Cells(currentRow, "M").Formula = .Cells(currentRow - 1, "M").Formula[/COLOR] 
            .Cells(currentRow, "B").Value = wsSource.Range("G17").Value
            .Cells(currentRow, "C").Value = wsSource.Range("G19").Value
            .Cells(currentRow, "D").Value = wsSource.Range("G13").Value
            .Cells(currentRow, "E").Value = wsSource.Range("G11").Value
            .Cells(currentRow, "F").Value = wsSource.Range("B11").Value
            .Cells(currentRow, "G").Value = wsSource.Cells(rng.Row, "A").Value
            .Cells(currentRow, "H").Value = wsSource.Cells(rng.Row, "B").Value
            .Cells(currentRow, "I").Value = wsSource.Cells(rng.Row, "C").Value
            .Cells(currentRow, "J").Value = wsSource.Cells(rng.Row, "D").Value
            .Cells(currentRow, "K").Value = wsSource.Cells(rng.Row, "E").Value
            .Cells(currentRow, "L").Value = wsSource.Cells(rng.Row, "F").Value
            .Cells(currentRow, "N").Value = wsSource.Range("B13").Value
            .Cells(currentRow, "O").Value = wsSource.Range("B15").Value
            .Cells(currentRow, "P").Value = wsSource.Range("B17").Value
            .Cells(currentRow, "Q").Value = wsSource.Range("G15").Value
        End With
 
        currentRow = currentRow + 1
    Next rng
 
    wsDest.Cells(firstRow, 1).EntireRow.Copy
    wsDest.Cells(firstRow + 1, 1).Resize(currentRow - firstRow - 1).EntireRow.PasteSpecial xlPasteFormats
    Application.GoTo wsDest.Cells(firstRow + 1, 1), True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
 
    MsgBox "Done!", vbInformation
End Sub

I have highlighted where I think this needs to be included? but really not sure?

 

[VoG]

I can't really test all that but I tried this quick check and it ran without error and produced the desired effect

Sub b()
Dim currentrow
currentrow = 2
With ActiveSheet
    With .Cells(currentrow - 1, "M")
        .AutoFill Destination:=.Resize(2)
    End With
End With
End Sub