PDA

View Full Version : [SOLVED] VBA Macro Stops at line 986



mge31624
12-14-2017, 09:57 AM
Good Afternoon All,

I am an amateur at VBA but need some assistance because a tool we use to scrub data stopped working and the creator is no longer with the company, so any suggestions would greatly be appreciated. I Keep running into an issue when I go to scrub the data I added from an export.....The line it keeps getting caught up on is:

i = 2
While Sheets("Raw").Range("B" & i).Value <> "" ' Stops when it gets to a blank cell

' This is done down every row then across to the next one for all 69 original data columns
For x = 1 To 69
Sheets("Raw").Cells(i, x) = Trim(Replace(Replace(Sheets("Raw").Cells(i, x), Chr(13), ""), Chr(10), ""))
Next

i = i + 1
Wend

Everything worked fine and no variables have changed up until this week. It seem whenever I place more than 985 lines of data, this causes the issue. I can choose any of the data I have, as long as it doesn't exceed that many lines. This was never a problem before this week.....any suggestions?

p45cal
12-14-2017, 10:51 AM
What error is being reported?

mge31624
12-14-2017, 11:20 AM
The error that I get when I run the full data is a Run-time error of 1004. However, anytime I use less than 986 lines of data, it works fine, regardless of the data.

SamT
12-14-2017, 12:03 PM
For i = LastRow to 2 Step -1
For j = 1 to 69
If Cells(1,j) = "" Then GoTo JNext
Sheets("Raw").Cells(i, x) = Trim(Replace(Replace(Sheets("Raw").Cells(i, x), Chr(13), ""), Chr(10), ""))
JNext:
Next j
Next i

p45cal
12-14-2017, 12:13 PM
I'm giving this a try but it's going to take more than an hour to reach that row!

Does the sheet have protection switched on?
What version of Excel are you using?



When it stops, you can select the cell with this comand in the immediate pane:
Application.Goto Sheets("Raw").Cells(i, x)
Is there something different about this cell or the cell to the left of it? For example, does it have a large number of characters? does it have any unusual characters?

Have you Dimmed i and x? If so how?

It's very slow, and there are probably much quicker methods (search and replace on the whole range).

While…Wend is passé, Do While…Loop affords you greater control by being able to break out of it more easily.

mge31624
12-14-2017, 12:47 PM
Thank you, p45cal...in answer to your questions...
Sheet protection is off...
Excel 2013......
There is nothing different in that particular row that should be triggering it...I can put any of my lines of data after cell 985 and does not work...
They are both Dimmed as Integer
I will try the Do while and Loop as well...

mge31624
12-14-2017, 12:57 PM
Actually p45cal I tried what SamT had posted and that fixed what was happening. Thank you both very much for your time and help. Just learning this stuff on my own...Much appreciated both your time and expertise

p45cal
12-14-2017, 02:39 PM
I'd be interested if you could do a test (I got no error here by the way after the hour was up) and see what speed you get out of this:
With Sheets("Raw")
With .Range(.Range("B2"), .Range("B2").End(xlDown)).Offset(, -1).Resize(, 69)
.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With
End With

mge31624
12-14-2017, 03:13 PM
p45cal, That worked!!! Thank you.

p45cal
12-14-2017, 03:40 PM
p45cal, That worked!!! Thank you.Yes, but was it any faster?

mge31624
12-14-2017, 03:55 PM
It actually took about the same time. Perhaps slightly faster than the other work around....

SamT
12-15-2017, 09:25 AM
Adapting p45cal's code to an array for speed


Dim Tmp '(as variant)

With Sheets("Raw")
Tmp = .Range(.Range("B2"), .Range("B2").End(xlDown)).Offset(, -1).Resize(, 69).Value

With tmp
.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With
.Range(.Range("B2"), .Range("B2").End(xlDown)).Offset(, -1).Resize(, 69).Value = Tmp
End With