Auto Populate not working

jaeremata

New Member
Joined
Jan 20, 2021
Messages
22
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi everyone! Hope everyone is doing great. I need your help regarding with my code. What it do is to automatically populate the column AJ - AO with the formula. But it's weird because it goes in the middle instead on the row. If you can see the SS, it goes in the middle but it should start on row 3. Can anyone help and correct what I need to do with my code.



VBA Code:
Sub Populate_Formulas()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False


Dim lrow As Long
Dim usedRows As Long
Dim WS As Worksheet


Worksheets("List").Select
Set WS = ThisWorkbook.Worksheets("List")
 
    '-------------------------Find number of lines with NC data
    usedRows = WS.Cells(WS.Rows.Count, "G").End(xlUp).Row
    lrow = Cells(Rows.Count, "AI").End(xlUp).Row
    lrow = lrow + 1
    
    '-------------------------Fill the formulas
 
    
    Cells(lrow, "AJ").FormulaR1C1 = "=IF(RC[-9]=""No"","""",IF(RC[-4]="""",""On-Going"",IF(RC[-13]<>"""",IF(RC[-7]<>"""",NETWORKDAYS(RC[-7],RC[-4])-1,NETWORKDAYS(RC[-13],RC[-4])-1),"""")))"
    Cells(lrow, "AK").FormulaR1C1 = "=IF(RC[-10]=""No"","""",IF(RC[-26]-RC[-27]<0,""On going"",NETWORKDAYS(RC[-27],RC[-26])-1))"
    Cells(lrow, "AL").FormulaR1C1 = "=IF(RC[-11]=""No"","""",IF(OR(RC[-24]<>""EX"",RC[-10]="""",RC[-15]=""""),"""",IF((NETWORKDAYS(RC[-15],RC[-10])-1)<=2,""On-Time"",""Not"")))"
    Cells(lrow, "AM").FormulaR1C1 = "=IF(RC[-12]=""No"","""",IF(RC[-3]="""","""",IF(RC[-3]=""On-Going"",""On-Going"",IF(RC[-3]<=60,""OnTime"",IF(RC[-3]<100,""60-100"",""Not on Time"")))))"
    Cells(lrow, "AN").FormulaR1C1 = "=MONTH(RC[-30])"
    Cells(lrow, "AO").FormulaR1C1 = "=YEAR(RC[-31])"
 
    Range(Cells(lrow, "AJ"), Cells(lrow, "AO")).Select   
    

Application.DisplayStatusBar = True
Application.ScreenUpdating = True


 
End Sub
 

Attachments

  • 1614087810127.png
    1614087810127.png
    23.8 KB · Views: 4

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Range("AJ3:AJ" & lrow).FormulaR1C1 = "=IF(RC[-9]=""No"","""",IF(RC[-4]="""",""On-Going"",IF(RC[-13]<>"""",IF(RC[-7]<>"""",NETWORKDAYS(RC[-7],RC[-4])-1,NETWORKDAYS(RC[-13],RC[-4])-1),"""")))"
 

jaeremata

New Member
Joined
Jan 20, 2021
Messages
22
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thank you anyway. I just found what is causing the issue.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,017
Messages
5,628,153
Members
416,296
Latest member
smartua

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top