Repeat code through for loop giving error

earthworm

Active Member
Joined
May 19, 2009
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I want the below code to repeat 38 times . I have also mapped all variable reference but still its not working
please help what am i doing wrong in this code

VBA Code:
Sub Split8()

Windows("Raw Report Under Construction.xlsb").Activate

Sheets("DB").Select

Dim x As Integer
For x = 1 To 38

Dim Filter As String
Filter = Range("G" & x).Value

Dim Filename As String
Filename = Range("H" & x).Value

Dim reference As Integer
reference = Range("I" & x).Value

    Sheets("DB").Select
    If reference > 0 Then
    Sheets("Sheet1").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AF$26443").AutoFilter Field:=4, Criteria1:=Filter
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.Columns.AutoFit
    Columns("A:A").Select
    Selection.NumberFormat = "General"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R2C[3]:RC[3],RC[3])"
    Range("A2").Copy
    Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).PasteSpecial
    Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    Range("A1").Select
            Sheets("Sheet1").name = Filter
        ChDir "C:\Users\iiiii\OneDrive\Desktop\Test"
    ActiveWorkbook.SaveAs Filename:=Filename, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    ActiveWorkbook.Close
    Windows("Raw Report Under Construction.xlsb").Activate
    Range("A1").Select
    Selection.AutoFilter
    End If
    Next x
    End Sub
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

earthworm

Active Member
Joined
May 19, 2009
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
on first time it runs correctly however on second time the macro is giving error at

VBA Code:
reference = Range("I" & x).Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
What error?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You said it was giving an error on the 2nd loop. What was the error?
 

earthworm

Active Member
Joined
May 19, 2009
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You said it was giving an error on the 2nd loop. What was the error?
Runtime error '13':
Type mismatch on below code

VBA Code:
reference = Range("I" & x).Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case what is in that cell? Is it text?
 

earthworm

Active Member
Joined
May 19, 2009
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
In that case what is in that cell? Is it text?
Numeric value moreover i have checked the watched window its showing the value of 83 i was expecting value between 1 and 38.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You would not get that error if it was a numeric value.
What is in I2 on sheet1?
 

earthworm

Active Member
Joined
May 19, 2009
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You would not get that error if it was a numeric value.
What is in I2 on sheet1?
it contains a numeric count formula like 0 or numeric value .
Basically whenever there is a value > 0 between range I2 and 39 than i want the macro to run . the macro looks for value in each cell between I2 and I39 and if it finds value >0 then it will run and perform function


I2 is on another sheet by the name of DB
Sheets("DB").Select
If reference > 0 Then
 

Watch MrExcel Video

Forum statistics

Threads
1,128,130
Messages
5,628,871
Members
416,347
Latest member
AT2021

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