Repeat code through for loop giving error

earthworm

Well-known Member
Joined
May 19, 2009
Messages
759
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
on first time it runs correctly however on second time the macro is giving error at

VBA Code:
reference = Range("I" & x).Value
 
Upvote 0
You said it was giving an error on the 2nd loop. What was the error?
 
Upvote 0
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
 
Upvote 0
In that case what is in that cell? Is it text?
 
Upvote 0
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.
 
Upvote 0
You would not get that error if it was a numeric value.
What is in I2 on sheet1?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,724
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top