VBA Error 400

W

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
Can someone please tell me where the problem is in this code. I get an error 400 when i run it.

Code: 
Sub ImportInvoices()

    fx = Dir("g:\marketing\invoices\" & "MISC*.xls")
    Hx = Sheets("MAIN REGISTER").Cells(Rows.Count, 1).End(xlUp).Row
    Ix = Hx + 1
    Jx = CInt(Mid(Sheets("MAIN REGISTER").Cells(Hx, 1), 5, 4))
      
    Cells(Ix, 1).Select
    Kx = Ix
    Do While Len(fx) > 0
        Gx = CInt(Mid(fx, 5, 4))
        If Gx > Jx Then
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Selection.EntireRow.Insert
            Limit = Cells(Kx, Columns.Count).End(xlToLeft).Column
            ActiveCell.Formula = "MISC" & Gx
            ActiveCell.Offset(0, 2).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$R$13"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$D$12"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$D$12"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$E$31"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$A$21"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$D$11"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$E$39"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$E$40"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$S$45"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            
            ActiveCell.Offset(1, 0).Select
            Kx = Kx + 1
            Cells(Kx, 1).Select
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
        End If
        fx = Dir()
        
    Loop
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
W

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
Well - sometimes it tells me the variables aren't defined, and sometimes it gives an error 400. Always errors out towards the top.

This script was working yesterday - which i find really odd.

Also, if i run the script on a blank worksheet it does the job.
 
Upvote 0
W

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
Code: 
Fx = Dir("g:\marketing\invoices\" & "MISC*.xls")

I'm getting variable not defined - for all my variables.

Now, I know how to define them, but i don't know what to define them as. Below is as far as i've got.

Code: 
Dim Limit as Long
 
Upvote 0
Andrew Poulsom

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Do you have Option Explicit at the top of your module. You can remove it to avoid having to declare all your variables, although I wouldn't recommend it.

If you don't know what type your variable is you can just:

Dim MyVariable

That makes it a Variant.
 
Upvote 0
W

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
OK, I binned the Option Explicit to see how that fixed things, as i only added that in today.

It now throws up an error 400 on the very first line??

Code: 
Sub ImportInvoices()

    Fx = Dir("g:\marketing\invoices\" & "MISC*.xls")
    Hx = Sheets("MAIN REGISTER").Cells(Rows.Count, 1).End(xlUp).Row
    Ix = Hx + 1
    Jx = CInt(Mid(Sheets("MAIN REGISTER").Cells(Hx, 1), 5, 4))
      
    Cells(Ix, 1).Select
    Kx = Ix
    Do While Len(Fx) > 0
        Gx = CInt(Mid(Fx, 5, 4))
        If Gx > Jx Then
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Selection.EntireRow.Insert
            Limit = Cells(Kx, Columns.Count).End(xlToLeft).Column
            ActiveCell.Formula = "MISC" & Gx
            ActiveCell.Offset(0, 2).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$R$13"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$D$12"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$D$12"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$E$31"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$A$21"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$D$11"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$E$39"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$E$40"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Formula = "=[MISC" & Gx & ".xls]Sheet1!$S$45"
            ActiveCell.Copy
            ActiveCell.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 1).Select
            
            ActiveCell.Offset(1, 0).Select
            Kx = Kx + 1
            Cells(Kx, 1).Select
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
        End If
        Fx = Dir()
        
    Loop
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Copying and Pasting CommentThreaded
Replies
8
Views
199
mayoung
M
C
  • Question
Find function loop with True and False outcomes
Replies
1
Views
183
jasonb75
J
R
  • Question
Copy a specific worksheet to only certain files in a folder
Replies
4
Views
196
HaHoBe
HaHoBe
R
  • Question
listing a file name when the file is missing
Replies
6
Views
271
rollis13
rollis13
S
  • Solved
VBA macro-is there a faster way to copy-paste special?
2
Replies
11
Views
1K
simbah
S

Forum statistics

Threads
1,191,214
Messages
5,985,312
Members
439,957
Latest member
venky2002

Share this page

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