Forum Discussion

gms4b's avatar
gms4b
Brass Contributor
Sep 04, 2019
Solved

help making a loop (to automate table formatting)

I am using excel VBA to automate table formatting. I have 24 tables to automate. I wrote the code with a macro then made some changes. It works fine (it autofills data on A3, E3, and F3, based on the...
Formulas and Functions
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Sep 05, 2019

    gms4b 

     

    This should do what you are trying to achieve....

     

    Sub FormatTables()
Dim c   As Long
Dim lr  As Long
Dim i   As Long

Application.ScreenUpdating = False

c = 1

For i = 1 To 24
    lr = Cells(Rows.Count, c + 1).End(xlUp).Row
    If lr > 2 Then
        Cells(3, c).AutoFill Destination:=Range(Cells(3, c), Cells(lr, c))
        Cells(3, c + 4).AutoFill Destination:=Range(Cells(3, c + 4), Cells(lr, c + 4))
        Cells(3, c + 5).AutoFill Destination:=Range(Cells(3, c + 5), Cells(lr, c + 5))
    End If
    c = c + 18
Next i

Application.ScreenUpdating = True
End Sub
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Sep 06, 2019

gms4b 

 

The problem is with the following two lines...

1. Range(.Cells(3, sc), .Cells(37, ec)).Select

2. Range(.Cells(fr, 1)).Select

 

In the first line you are using . (period) before the Cells which is used when you specify the parent object in a WITH and END WITH block so it is not required here and you should remove the period from both the occurrences of Cells in that line. Also, you don't need to use Select in order to copy a range so you can replace Select with Copy.

 

In second line Range object expects a cell address not the cell object itself so use of Range in that line doesn't make any sense, you can use only Cells(fr, 1).Select and that will work as desired.

 

Please try it like this...

 

Sub movecharts()
Dim sr As Long
Dim er As Long
Dim ec As Long
Dim sc As Long
Dim i As Long
Dim fr As Long

Application.ScreenUpdating = False

sc = 19
ec = 24
fr = 35

For i = 1 To 26
    Range(Cells(3, sc), Cells(37, ec)).Copy
    Cells(fr, 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = 0

    sc = sc + 18
    sr = sr + 18
    fr = fr + 35

Next i

Application.ScreenUpdating = True
End Sub
gms4b's avatar
gms4b
Brass Contributor
Sep 06, 2019

Subodh_Tiwari_sktneer 

 

Perfect fix! Thanks so much! Obviously, I'm not a programmer, just a chemist trying to automate reports that my team and I do over and over again. This will help so much!

 

There was a mistake in the code I fixed....(needed to fix the increment at the end (ec = ec + 18)). I also didn't need some of the variables I initially included ("sr" and "er", so I deleted them). Also, I ran this directly after another loop so I changed "i" to "j". I have the final code included below for completeness. 

 

Dim ec As Long
Dim sc As Long
Dim j As Long
Dim fr As Long

Application.ScreenUpdating = False

sc = 19
ec = 24
fr = 35

For j = 1 To 26
    Range(Cells(3, sc), Cells(37, ec)).Copy
    Cells(fr, 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = 0

    sc = sc + 18
    ec = ec + 18
    fr = fr + 35

Next j

Application.ScreenUpdating = True

 

 

Resources