VBA For Each Formula & Autofill

[Nancy123]

Dear All,
Hello, I've just learnt how to use VBA and still new on it. I made a formula below, where it works perfectly for my sheet.

Range("$AE$2").FormulaR1C1 = "=IF(RC[-25]=""V6"",LEFT(RC[-11],LEN(RC[-11])-1),RC[-11])"
  Range("AE2").AutoFill Destination:=Range("AE2:AE" & Range("F" & Rows.Count).End(xlUp).Row)
    
  Columns("$AE").Copy
  Columns("$AF").PasteSpecial Paste:=xlPasteValues
    [AF:AF].Select
    With Selection
        .NumberFormat = "General"
        .Value = .Value
        .Style = "Comma"
        
    End With
    
   Columns("$AF").Cut Columns("$T")
   Range("$T$1") = "DPP"
   Range("$T$1").Font.Bold = True
  
   Columns("AE").ClearContents

Anyway, I want this code to run in every sheet I have until the last sheet in my workbook. I've learnt that we must use the For Each formula. I've tried it but the code didn't work properly and I think I've made a lot of mistakes. Here is what I use :

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

  ws.Range("$AE$2").FormulaR1C1 = "=IF(RC[-25]=""V6"",LEFT(RC[-11],LEN(RC[-11])-1),RC[-11])"
  ws.Range("AE2").AutoFill Destination:=Range("AE2:AE" & Range("F" & Rows.Count).End(xlUp).Row)
    
  ws.Columns("$AE").Copy
  ws.Columns("$AF").PasteSpecial Paste:=xlPasteValues
    [AF:AF].Select
    With Selection
        .NumberFormat = "General"
        .Value = .Value
        .Style = "Comma"
        
    End With
    
   ws.Columns("$AF").Cut Columns("$T")
   ws.Range("$T$1") = "DPP"
   ws.Range("$T$1").Font.Bold = True
   
   ws.Columns("AE").ClearContents

Next ws

Kindly need your help to tell me what is the correct code
Thanks a lot

 

[aRandomHelper]

You're pretty close:

ws.[AF:AF].Select

Or, even better:

  ws.Columns("$AE").Copy
  ws.Columns("$AF").PasteSpecial Paste:=xlPasteValues

    With ws.Columns("AF")
        .NumberFormat = "General"
        .Value = .Value
        .Style = "Comma"
        
    End With

You don't have to select a range to make changes to it with VBA.

 

[Nancy123]

Hi *aRandomHelper

Thanks for helping me. However when I run it, it already get stuck in the autofill formula

 ws.Range("AE2").AutoFill Destination:=Range("AE2:AE" & Range("F" & Rows.Count).End(xlUp).Row)

That formula works fine if I just use it on one sheet. But when I use it with the For Each formula, it caused an error.
Do you know what is wrong with that formula?
Thanks.

 

[aRandomHelper]

Right, apologies for that, I did not notice that part.

ws.Range("AE2").AutoFill Destination:=ws.Range("AE2:AE" & ws.Range("F" & Rows.Count).End(xlUp).Row)

Qualify the Ranges with ws. object.

 

[Nancy123]

Dear *aRandomHelper

Thanks a lot for your help, it works now.

 

[MARK858]

@Nancy123 you can just apply the formula directly to the range rather than autofilling i.e.

ws.Range("AE2:AE" & ws.Range("F" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-25]=""V6"",LEFT(RC[-11],LEN(RC[-11])-1),RC[-11])"

rather than

ws.Range("$AE$2").FormulaR1C1 = "=IF(RC[-25]=""V6"",LEFT(RC[-11],LEN(RC[-11])-1),RC[-11])"
  ws.Range("AE2").AutoFill Destination:=ws.Range("AE2:AE" & ws.Range("F" & Rows.Count).End(xlUp).Row)