Hi Group,

In the code given below, ideally I would like to have
For k = i To 65536
but I get "Overflow" error during execution. So, I have "For k = i To
30000" Or could my code be tweaked to avoid this overflow error?
Please let me know.

Is there any way to overcome this "Overflow" error

Public Sub test1()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

Dim fname As String

i = 2
l = 2

For k = i To 30000
j = i + 1

If Range("B" & i).Value < Range("B" & j).Value Then
Range("B" & l, "Z" & i).Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\" & Range("A1").Value & ".txt", FileFormat:= _
xlUnicodeText, CreateBackup:=False

ActiveWindow.Close

l = j
End If

If Range("B" & j).Value = "" Then
Exit Sub
End If

i = i + 1
Next k

End Sub

Thanks,
Kevin

Try declaring your variables as Long instead of Integer. That might help.

" wrote:

Hi Group,

In the code given below, ideally I would like to have
For k = i To 65536
but I get "Overflow" error during execution. So, I have "For k = i To
30000" Or could my code be tweaked to avoid this overflow error?
Please let me know.

Is there any way to overcome this "Overflow" error

Public Sub test1()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

Dim fname As String

i = 2
l = 2

For k = i To 30000
j = i + 1

If Range("B" & i).Value < Range("B" & j).Value Then
Range("B" & l, "Z" & i).Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\" & Range("A1").Value & ".txt", FileFormat:= _
xlUnicodeText, CreateBackup:=False

ActiveWindow.Close

l = j
End If

If Range("B" & j).Value = "" Then
Exit Sub
End If

i = i + 1
Next k

End Sub

Thanks,
Kevin

On Jul 28, 5:27*pm, JLGWhiz wrote:
Try declaring your variables as Long instead of Integer. *That might help.



" wrote:
Hi Group,

In the code given below, ideally I would like to have
For k = i To 65536
but I get "Overflow" error during execution. So, I have "For k = i To
30000" Or could my code be tweaked to avoid this overflow error?
Please let me know.

Is there any way to overcome this "Overflow" error

Public Sub test1()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

Dim fname As String

i = 2
l = 2

For k = i To 30000
j = i + 1

If Range("B" & i).Value < Range("B" & j).Value Then
Range("B" & l, "Z" & i).Select
Selection.Copy
Workbooks.Add
* * Range("A1").Select
* * ActiveSheet.Paste
* * Application.CutCopyMode = False
* * Application.DisplayAlerts = False

* * ActiveWorkbook.SaveAs Filename:= _
* * * * "C:\" & Range("A1").Value & ".txt", FileFormat:= _
* * * * xlUnicodeText, CreateBackup:=False

* * ActiveWindow.Close

l = j
End If

If Range("B" & j).Value = "" Then
Exit Sub
End If

i = i + 1
Next k

End Sub

Thanks,
Kevin- Hide quoted text -

- Show quoted text -

Thank you. It worked.