CODEWITHSUNDEEP
excelvba
Bluesector
Bluesector

Reputation: 329

Row count and insert function

I'm using the following code to copy from an excel file starting with "Backorder Details": 

Sub INSERT()

Dim Wb1 As Workbook, wb2 As Workbook, wB As Workbook
Dim rngToCopy As Range
Dim countEND As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("RAW_DATA").Select

Worksheets("RAW_DATA").Range("A3:CA45000").ClearContents
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

For Each wB In Application.Workbooks
    If Left(wB.Name, 17) = "Backorders Detail" Then
        Set Wb1 = wB
        Exit For
    End If
Next

If Not Wb1 Is Nothing Then
    Set wb2 = ThisWorkbook

    With Wb1.Sheets(2)
        Set rngToCopy = .Range("A3:BX3", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    wb2.Sheets("RAW_DATA").Range("A3:BX3").Resize(rngToCopy.Rows.Count).Value = rngToCopy.Value

End If

With wb2.Sheets("RAW_DATA")
    countEND = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("BY3:BY" & countEND).Formula = "=IFERROR(IF(AND(VLOOKUP(VALUE(AS3);BG_DATA!A:A;1;FALSE)=(VLOOKUP(VALUE(AS3);BG_DATA!A:A;1;FALSE));(AF3>0));"""";""x"");""x"")" 'Check
    .Range("BZ3:BZ" & countEND).Formula = "=VLOOKUP(VALUE(AS3);BG_DATA!A:I;9;FALSE)" 'Vendor'
    .Range("CA3:CA" & countEND).Formula = "=VLOOKUP(VALUE(AS3);BG_DATA!A:J;10;FALSE)" 'Planner
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "DONE!"

End Sub

I copied this function from another excel file I've made. It worked pretty good. But for some reason the following part is giving me an error now:

With wb2.Sheets("RAW_DATA")
    countEND = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("BY3:BY" & countEND).Formula = "=IFERROR(IF(AND(VLOOKUP(VALUE(AS3);BG_DATA!A:A;1;FALSE)=(VLOOKUP(VALUE(AS3);BG_DATA!A:A;1;FALSE));(AF3>0));"""";""x"");""x"")" 'Check
    .Range("BZ3:BZ" & countEND).Formula = "=VLOOKUP(VALUE(AS3);BG_DATA!A:I;9;FALSE)" 'Vendor'
    .Range("CA3:CA" & countEND).Formula = "=VLOOKUP(VALUE(AS3);BG_DATA!A:J;10;FALSE)" 'Planner
End With

Run-time error 1004 - Application-defined or object defined error. I googled this but didn't find a solution. Excel file was an XLSX and is an XLSM now. Can someone please tell what the problem is?

Upvotes: 0

Views: 50

Answers (1)

Alexey
Alexey

Reputation: 1539

You are using semicolon ; as a delimiter in your formula, but the delimiter depends on the localization of your workbook. Use comma , instead:

With wb2.Sheets("RAW_DATA")
    countEND = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("BY3:BY" & countEND).Formula = "=IFERROR(IF(AND(VLOOKUP(VALUE(AS3),BG_DATA!A:A,1,FALSE)=(VLOOKUP(VALUE(AS3),BG_DATA!A:A,1,FALSE)),(AF3>0)),"""",""x""),""x"")" 'Check
    .Range("BZ3:BZ" & countEND).Formula = "=VLOOKUP(VALUE(AS3),BG_DATA!A:I,9,FALSE)" 'Vendor'
    .Range("CA3:CA" & countEND).Formula = "=VLOOKUP(VALUE(AS3),BG_DATA!A:J,10,FALSE)" 'Planner
End With

Alternatively you could use .FormulaLocal instead of .Formula, but it will work only in the workbooks where localization defines semicolon as the delimiter.

Upvotes: 1

Related Questions