VBA For Each Formula & Autofill

Nancy123

New Member
Joined
Jan 15, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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 :

VBA Code:
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

aRandomHelper

Board Regular
Joined
Jan 14, 2021
Messages
238
Office Version
  1. 2016
Platform
  1. Windows
You're pretty close:
VBA Code:
ws.[AF:AF].Select

Or, even better:
VBA Code:
  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

New Member
Joined
Jan 15, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi *aRandomHelper

Thanks for helping me. However when I run it, it already get stuck in the autofill formula
VBA Code:
 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

Board Regular
Joined
Jan 14, 2021
Messages
238
Office Version
  1. 2016
Platform
  1. Windows
Right, apologies for that, I did not notice that part.

VBA Code:
ws.Range("AE2").AutoFill Destination:=ws.Range("AE2:AE" & ws.Range("F" & Rows.Count).End(xlUp).Row)
Qualify the Ranges with ws. object.
 

Nancy123

New Member
Joined
Jan 15, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Dear *aRandomHelper

Thanks a lot for your help, it works now.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Nancy123 you can just apply the formula directly to the range rather than autofilling i.e.
VBA Code:
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
VBA Code:
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,089
Messages
5,628,609
Members
416,327
Latest member
Chimay

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