VBA Code to insert a soft coded formula in the last row of data

[AV_Geek]

So - I have a soft coded formula that I want inserted in a cell in the last row that contains data, and ONLY the last row

For example: If my last Row with Data is Row 9, I would like the formula in cell P9 to calculate =J9 + K9
Likewise, if my last row of data is in Row 17, I would like the formula in cell P17 to calculate = J17 + K17
Likewise, if my last row of data is in Row *, I would like the formula in cell P17 to calculate = J* + K*

I tried to use

Sub Last()
'Name of Macro
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("F1").Formula = "=IF(C1>10,""A"",""B"")"
Range ("F" & (lr))
End Sub

But it's taking the data from N1 instead of N*. Any suggestions? I also googled for a way to soft code the row number based off of the current row, but couldn't find anything. The ROW function didn't do me any good.

The other thing I can think of is to copy it all of the way down to the last row. and then use another macro to clear all of the rows down to the row above it. But, it's getting late. I'll try that in a few days when I get back to my project, but any other ideas until them would be appreciated.

 

[mohadin]

Hi
See if this can help some how

Sub Last()
'Name of Macro
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Range("F" & (lr)).Formula = "=IF(C" & lr & ">10,""A"",""B"")"
    'Range ("F" & (lr))

End Sub

 

[AV_Geek]

Hi - I'm back. Sorry I was unresponsive for a week - I got tied up in another project.

The formula I gave you was supposed to be for symplicity of this thread, but it did not work.

Here are the two real formulas that I need this to work for.

"=IF(OR(MID(N*,10,2)=""ON"",MID(N*,15,5)=""EARLY"",MID(N*,16,5)=""EARLY"",MID(N*,20,5)=""EARLY"",MID(N*,21,5)=""EARLY"",MID(N*,22,5)=""EARLY""),""0"",IF(MID(N*,13,2)=""HR"",(60*(MID(N*,10,2))),IF(MID(N*,12,2)=""HR"",(60*(MID(N*,10,2))),0)))"

"=IF(AND((MID(N*,12,2)=""MI""),(MID(N*,15,4)=""LATE"")),MID(N*,10,1),IF(AND((MID(N*,13,2)=""MI""),(MID(N*,16,4)=""LATE"")),MID(N*,10,2),IF(AND((MID(N*,17,2)=""MI""),(MID(N*,20,4)=""LATE"")),MID(N*,15,1),IF(AND((MID(N*,18,2)=""MI""),(MID(N*,21,4)=""LATE""),(MID(N*,12,2)=""HR"")),MID(N*,15,2),IF(AND((MID(N*,18,2)=""MI""),(MID(N*,21,4)=""LATE""),(MID(N*,13,2)=""HR"")),MID(N*,16,1),IF(AND((MID(N*,19,2)=""MI""),(MID(N*,22,4)=""LATE"")),MID(N*,16,2),""0""))))))"

I substituted N" & lr & " for N*

"=IF(OR(MID(N” & lr & “,10,2)=""ON"",MID(N” & lr & “,15,5)=""EARLY"",MID(N” & lr & “,16,5)=""EARLY"",MID(N” & lr & “,20,5)=""EARLY"",MID(N” & lr & “,21,5)=""EARLY"",MID(N” & lr & “,22,5)=""EARLY""),""0"",IF(MID(N” & lr & “,13,2)=""HR"",(60*(MID(N” & lr & “,10,2))),IF(MID(N” & lr & “,12,2)=""HR"",(60*(MID(N” & lr & “,10,2))),0)))"

"=IF(AND((MID(N” & lr & “,12,2)=""MI""),(MID(N” & lr & “,15,4)=""LATE"")),MID(N” & lr & “,10,1),IF(AND((MID(N” & lr & “,13,2)=""MI""),(MID(N” & lr & “,16,4)=""LATE"")),MID(N” & lr & “,10,2),IF(AND((MID(N” & lr & “,17,2)=""MI""),(MID(N” & lr & “,20,4)=""LATE"")),MID(N” & lr & “,15,1),IF(AND((MID(N” & lr & “,18,2)=""MI""),(MID(N” & lr & “,21,4)=""LATE""),(MID(N” & lr & “,12,2)=""HR"")),MID(N” & lr & “,15,2),IF(AND((MID(N” & lr & “,18,2)=""MI""),(MID(N” & lr & “,21,4)=""LATE""),(MID(N” & lr & “,13,2)=""HR"")),MID(N” & lr & “,16,1),IF(AND((MID(N” & lr & “,19,2)=""MI""),(MID(N” & lr & “,22,4)=""LATE"")),MID(N” & lr & “,16,2),""0""))))))"

So, I have the two macros:

Sub Formula5()
'Number of Hours Late For Last Row - One Time Rows
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("P" & (lr)).Formula = "=IF(OR(MID(N” & lr & “,10,2)=""ON"",MID(N” & lr & “,15,5)=""EARLY"",MID(N” & lr & “,16,5)=""EARLY"",MID(N” & lr & “,20,5)=""EARLY"",MID(N” & lr & “,21,5)=""EARLY"",MID(N” & lr & “,22,5)=""EARLY""),""0"",IF(MID(N” & lr & “,13,2)=""HR"",(60*(MID(N” & lr & “,10,2))),IF(MID(N” & lr & “,12,2)=""HR"",(60*(MID(N” & lr & “,10,2))),0)))"
End Sub

and

Sub Formula6()
'Number of Minutes Late For Last Row - One Time Rows
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("Q" & (lr)).Formula = "=IF(AND((MID(N” & lr & “,12,2)=""MI""),(MID(N” & lr & “,15,4)=""LATE"")),MID(N” & lr & “,10,1),IF(AND((MID(N” & lr & “,13,2)=""MI""),(MID(N” & lr & “,16,4)=""LATE"")),MID(N” & lr & “,10,2),IF(AND((MID(N” & lr & “,17,2)=""MI""),(MID(N” & lr & “,20,4)=""LATE"")),MID(N” & lr & “,15,1),IF(AND((MID(N” & lr & “,18,2)=""MI""),(MID(N” & lr & “,21,4)=""LATE""),(MID(N” & lr & “,12,2)=""HR"")),MID(N” & lr & “,15,2),IF(AND((MID(N” & lr & “,18,2)=""MI""),(MID(N” & lr & “,21,4)=""LATE""),(MID(N” & lr & “,13,2)=""HR"")),MID(N” & lr & “,16,1),IF(AND((MID(N” & lr & “,19,2)=""MI""),(MID(N” & lr & “,22,4)=""LATE"")),MID(N” & lr & “,16,2),""0""))))))"
End Sub

But I get errored out.

 

[mohadin]

Hi
Se if this work for you

Sub Formula5()
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Range("P" & (lr)).Formula = "=IF(OR(MID(N" & lr & ",10,2)=""ON"",MID(N" & lr & ",15,5)=""EARLY"",MID(N" & lr & ",16,5)=""EARLY"",MID(N" & lr & ",20,5)=""EARLY"",MID(N" & lr & ",21,5)=""EARLY"",MID(N" & lr & ",22,5)=""EARLY""),""0"",IF(MID(N" & lr & ",13,2)=""HR"",(60*(MID(N" & lr & ",10,2))),IF(MID(N" & lr & ",12,2)=""HR"",(60*(MID(N" & lr & ",10,2))),0)))"
End Sub

Sub Formula6()
'Number of Minutes Late For Last Row - One Time Rows
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Range("Q" & (lr)).Formula = "=IF(AND((MID(N" & lr & ",12,2)=""MI""),(MID(N" & lr & ",15,4)=""LATE"")),MID(N" & lr & ",10,1),IF(AND((MID(N" & lr & ",13,2)=""MI""),(MID(N" & lr & ",16,4)=""LATE"")),MID(N" & lr & ",10,2),IF(AND((MID(N" & lr & ",17,2)=""MI""),(MID(N" & lr & ",20,4)=""LATE"")),MID(N" & lr & ",15,1),IF(AND((MID(N" & lr & ",18,2)=""MI""),(MID(N" & lr & ",21,4)=""LATE""),(MID(N" & lr & ",12,2)=""HR"")),MID(N" & lr & ",15,2),IF(AND((MID(N" & lr & ",18,2)=""MI""),(MID(N" & lr & ",21,4)=""LATE""),(MID(N" & lr & ",13,2)=""HR"")),MID(N" & lr & ",16,1),IF(AND((MID(N" & lr & ",19,2)=""MI""),(MID(N" & lr & ",22,4)=""LATE"")),MID(N" & lr & ",16,2),""0""))))))"
End Sub

 

[AV_Geek]

I think you nailed it on the head. Thanks a million.

 

[Peter_SSs]

You can physically shorten the code to enter those formulas a reasonable amount by using a placeholder (like you were doing with the asterisk) and substitute rather than splitting the string and entering the lr at every point.

Sub Formula5a()
  'Number of Hours Late For Last Row - One Time Rows
  lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  Range("P" & lr).Formula = Replace("=IF(OR(MID(N#,10,2)=""ON"",MID(N#,15,5)=""EARLY"",MID(N#,16,5)=""EARLY"",MID(N#,20,5)=""EARLY"",MID(N#,21,5)=""EARLY"",MID(N#,22,5)=""EARLY""),""0"",IF(MID(N#,13,2)=""HR"",(60*(MID(N#,10,2))),IF(MID(N#,12,2)=""HR"",(60*(MID(N#,10,2))),0)))", "#", lr)
End Sub

Sub Formula6a()
  'Number of Minutes Late For Last Row - One Time Rows
  lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  Range("Q" & lr).Formula = Replace("=IF(AND((MID(N#,12,2)=""MI""),(MID(N#,15,4)=""LATE"")),MID(N#,10,1),IF(AND((MID(N#,13,2)=""MI""),(MID(N#,16,4)=""LATE"")),MID(N#,10,2),IF(AND((MID(N#,17,2)=""MI""),(MID(N#,20,4)=""LATE"")),MID(N#,15,1),IF(AND((MID(N#,18,2)=""MI""),(MID(N#,21,4)=""LATE""),(MID(N#,12,2)=""HR"")),MID(N#,15,2),IF(AND((MID(N#,18,2)=""MI""),(MID(N#,21,4)=""LATE""),(MID(N#,13,2)=""HR"")),MID(N#,16,1),IF(AND((MID(N#,19,2)=""MI""),(MID(N#,22,4)=""LATE"")),MID(N#,16,2),""0""))))))", "#", lr)
End Sub

 

[mohadin]

I think you nailed it on the head.
You are very welcome
and thank you for the feedback
Be happy and safe

 

[AV_Geek]

OK, I'm going to throw another one at you if I may.

Right now, I'm on the sheet named "sheetB" and I have the following macro:

Sub RemoveNeg()
'Convert Negative to Zero
Range("S3").Formula = "=if(S2<1,""0"",S2)"
End Sub

Basically, inserting the formula =if(S2<1,"0",S2) into cell S3.

I want to add the following to it.

if on sheet "sheetA", the last cell in columns F or N = "START", Insert "T", if not, then if(S2<1,"0",S2)

So I tried the following formula

=IF(OR((LOOKUP(2,1/(sheetA!F:F<>""),sheetA!F:F)="*START*"),(LOOKUP(2,1/(sheetA!H:H<>""),sheetA!H:H)="*START*")),"T",(IF(S2<0,"0",S2)))

which put into a macro gives you

Sub RemoveNeg()
'Convert Negative Delay to Zero
Range("S3").Formula = "=IF(OR((LOOKUP(2,1/(sheetA!F:F<>""""),SheetA!F:F)=""START""),(LOOKUP(2,1/(sheetA!H:H<>""""),SheetA!H:H)=""START"")),""T"",(IF(S2<0,""0"",S2)))"
End Sub••••ˇˇˇˇ

but for some reason it keeps erroring as #N/A I figured out that it is because sometimes the alternate column is blank, meaning there isn't a last cell in the column.

Does anyone have any ideas as to how to handle this? I don't know how to write macros, but can easily insert one in if given. I'll post a link to this thread in the Formula's Help page of the forum, since it could be a formula issue.

 

[Peter_SSs]

inserting the formula =if(S2<1,"0",S2)

Are you sure that is what you want? That is a text "0" not a numerical 0?
In the code below I have assumed that you would want a numerical 0 if that part of the formula gets executed.
I have also assumed that any values in columns F or G of sheetA would be text values, not numerical. If this assumption is incorrect please post back with details.

Also, when posting vba code, please use one of the various vba code tags not quote tags. My signature block below has more information.

Try this

Range("S3").Formula = "=IF(OR(IFNA(LOOKUP(""zzz"",sheetA!F:F)=""START"",FALSE),IFNA(LOOKUP(""zzz"",sheetA!H:H)=""START"",FALSE)),""T"",IF(S2<0,0,S2))"