Error with Macro

[ian0886]

Hi Guys,

I have my macro below. On the line that is bolded and underline, I'm faced with a error message " Object variable or with block variable not set". I can't really figure what's missing in the variables that i've set. Appreciate the help!

Sub newspread()


    Dim wbMO2 As Workbook
    Set wbMO2 = Workbooks("family.xlsm") 'rem to change to trade tickets
    Dim trg As Worksheet, wsMO2(1 To 4) As Worksheet, colCount As Integer
     
    Set wsMO2(1) = wbMO2.Sheets("BP")
    Set wsMO2(2) = wbMO2.Sheets("TKY")
    Set wsMO2(3) = wbMO2.Sheets("family")


     
    Set trg = wbMO2.Worksheets.Add(After:=wbMO2.Worksheets(wbMO2.Worksheets.Count))
    trg.Name = "Master"
    
    Dim rng(1 To 2) As Range
    Set rng(1) = trg.UsedRange.Find("BNP", , xlValues, xlPart)
    
    For counter = 1 To 2
    With wsMO2(counter)
    .AutoFilterMode = False
    .Range("A2:I60").Copy
    End With
    
    With trg
        .Activate
        .Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
[U][B][I]        .Range("A1").CurrentRegion.AutoFilter Field:=rng(1).Column, Criteria1:=""[/I][/B][/U]
        .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
    Next counter
    
    trg.Range("a1").CurrentRegion.Copy
    
    With wsMO2(3)
        .Range("A2").Insert Shift:=xlDown
        .Columns.AutoFit
        .Columns("F:F").NumberFormat = "0.0000000"
        .Columns("B:B").NumberFormat = "d/mm/yyyy"
        .Columns("I:I").Value = .Columns("I:I").Value
    End With
End Sub

 

[Fluff]

What is the value of rng(1) when you get the error?

 

[Norie]

Have you checked that 'BNP' has actually been found?

If rng(1) Is Nothing Then
    MsgBox "BNP column not found!"
    Exit Sub
End If

PS Are you looking for the column with the header 'BNP'?

If so which row are the headers in?

 

[ian0886]

Yes Norie, I'm trying to find BNP in the column headers which is in column C

[TABLE="width: 580"]
<colgroup><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]43118[/TD]
[TD]BNP[/TD]
[TD]AUDUSD[/TD]
[TD]B/S[/TD]
[TD="align: right"]-6E-06[/TD]
[TD]Weekend[/TD]
[TD="align: right"]3[/TD]
[TD]-38.61[/TD]
[/TR]
</tbody>[/TABLE]

@Fluff - isn't the value "BNP", its suppose to filter and search that isn't it?


The funny thing is that when i click a cell in the "family" sheet the macro works exactly the way when i want it to. But when i check on the other sheets and run, it comes out with this error

 

[Norie]

How can you find 'BNP' on the sheet trg references?

You've just added that sheet so it's completely blank.

 

[ian0886]

hi norie,

data is copied and pasted from somewhere before the autofilter, so there is data for me to filter

.Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("A1").CurrentRegion.AutoFilter Field:=rng(1).Column, Criteria1:=""
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[Norie]

In this code you add the new worksheet, which is blank, you then try and find 'BNP' in the sheet.

    Set trg = wbMO2.Worksheets.Add(After:=wbMO2.Worksheets(wbMO2.Worksheets.Count))
    trg.Name = "Master"
    
    Dim rng(1 To 2) As Range
    Set rng(1) = trg.UsedRange.Find("BNP", , xlValues, xlPart)

'BNP' will not be found so rng(1) will be Nothing and that's why you get the error you describe here.

 .Range("A1").CurrentRegion.AutoFilter Field:=rng(1).Column, Criteria1:=""

 

[ian0886]

Hi Norie,

Don’t think that’s right. I wrote the macro to paste the data before the search for the header. This was running correctly until I started clicking on other sheets to try to run the macro again.

 

[Norie]

This is the start of the posted code, nothing is being pasted to the new sheet in this section of code.

Sub newspread()


    Dim wbMO2 As Workbook
    Set wbMO2 = Workbooks("family.xlsm") 'rem to change to trade tickets
    Dim trg As Worksheet, wsMO2(1 To 4) As Worksheet, colCount As Integer
     
    Set wsMO2(1) = wbMO2.Sheets("BP")
    Set wsMO2(2) = wbMO2.Sheets("TKY")
    Set wsMO2(3) = wbMO2.Sheets("family")


     
    Set trg = wbMO2.Worksheets.Add(After:=wbMO2.Worksheets(wbMO2.Worksheets.Count))
    trg.Name = "Master"
    
    Dim rng(1 To 2) As Range
    Set rng(1) = trg.UsedRange.Find("BNP", , xlValues, xlPart)

 

[ian0886]

Hi norie,

In fact what I showed you in post 4 is what was being copied and pasted