Excel VBA Macro Problem - Code used to work, now 400 error

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

stretch90

Thread Starter
Joined
Oct 13, 2011
Messages
3
Hey everyone,
So I used this code to copy a range of cells from multiple workbooks and paste it into one workbook. It used to work fine but now it gives me the 400 error. I've been adding files to the directory that it pull the files from and it was working fine, but all of a sudden it's causing this error. Can someone please help me out?

Dim xcell As Range
Dim ycell As Range
Dim sheetname As String
Dim wblist() As String
Dim i As Integer
Dim wbname As String
Dim j As Integer

i = 0
j = 0


FolderName = "C:\Documents and Settings\s.k\Desktop\CRs\LOG"
wbname = Dir(FolderName & "\" & "*.xls")

Application.ScreenUpdating = False

Do While wbname <> ""

i = i + 1
ReDim Preserve wblist(1 To i)
wblist(i) = wbname
wbname = Dir


Set ycell = Range(Cells(i + 3, 2), Cells(i + 2, 28))
Set xcell = Range(Cells(2, 3), Cells(2, 28))
sheetname = "loging form"

ycell.Formula = "=" & "'" & FolderName & "\[" & wblist(i) & "]" _
& sheetname & "'!" & xcell.Address


Loop

Do While j < 100
Cells(j + 3, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],4)"
'ActiveCell.FormulaR1C1 = "=LEFT[RC[6],4]"

Cells(3 + j, 1) = Val(Cells(3 + j, 1))
Cells(3 + j, 2).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[CR Status.xlsx]Sheet1'!R3C1:R189C3,3,FALSE)"

If Cells(3 + j, 1).Value = 0 Then
Cells(3 + j, 1).Value = ""
Cells(3 + j, 2).Value = ""
End If

j = j + 1


Loop


Application.CutCopyMode = False
Application.ScreenUpdating = True

Cells(1, 1).Select


I tried running through the steps and it looks like the error occured in the first loop right before the ycell.formula line and after it went through about 55 workbooks already.
Anyway, I know it's not very efficient code, but I just need it to work for now. Any help would be very appreciated. Thanks!
 
Joined
Jun 17, 2002
Messages
2,551
check your variables when it errors the check them just prior to the error. you need to identify the point at which a variable gets the wrong result.

Print them in the imedieate window like this...

?i

hit enter

also use watch window or locals.. debuging tips for excel vba.
 

stretch90

Thread Starter
Joined
Oct 13, 2011
Messages
3
Sorry, I'm not sure how to check the error values. It crashes at the ycell.formula line, so would that be because of the wblist(i) variable?
 
Joined
Jun 17, 2002
Messages
2,551
when you click Debug, the code window will light up yellow where it is stuck. now another way to see is to point your mouse over the variable and it will display what is stored in the variable.

study the results, all the variable words are declared with the DIM at the beginning of the code, so pointing to any of them will tell you their contents. but I like the immediate window.. so I copy the variable name and paste it with a question mark and hit enter... this will display the variable contents.

next I click the stop button (square) at top...and set a "Break point", you do this by clicking to the point at which you want your code to stop ( like beginning of a loop)...

eg..

Do While wbname <> "

click your mouse in the side bar immediately to the left of the word "Do" this will put a "dot" and highlight the line... now click the "Run" button ( play icon) and the code will stop here... check your variables... Press F8 to step through and every time the yellow line passes over a variable, the contents will update.... Press F5 to jump ahead.
 

stretch90

Thread Starter
Joined
Oct 13, 2011
Messages
3
Ok, I've checked the variable values and before it crashes wblist takes the proper filename, but for some reason it just gives me the 400 error. And this exact code used to work, even after adding a bunch of files, but when I added some more and now it's not working.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top