Excel Macro Runtime Error Overflow
Contents |
be down. Please try the request again. Your cache administrator is webmaster. Generated Sat, 15 Oct 2016 09:57:10 GMT by s_ac15 (squid/3.5.20)
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and visual basic runtime error 6 overflow excel policies of this site About Us Learn more about Stack Overflow the overflow error vba excel company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users runtime error 6 overflow vba access Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/run-time-error-6-overflow-during-macro-run/6a176497-c2b4-44e9-81c1-c921a71a5947 a minute: Sign up Run time error '6' OverFlow (Excel VBA) up vote 2 down vote favorite I am having this error, i have thousands of csv files to be loaded, it can only load about hundred files per time. Can anyone advise me where is the error? Option Explicit Function ImportData() Dim wkbCrntWorkBook As Workbook Dim wkbSourceBook As Workbook Dim rngSourceRange1 http://stackoverflow.com/questions/13466385/run-time-error-6-overflow-excel-vba As Range Dim rngSourceRange2 As Range Dim rngDestination1 As Range Dim rngDestination2 As Range Dim intColumnCount As Integer Set wkbCrntWorkBook = ActiveWorkbook Dim SelectedItemNumber As Integer Dim YesOrNoAnswerToMessageBox As String Dim Highest As Double Highest = 0 Dim counter As Integer Dim h1 As Integer Dim h2 As Integer h1 = 1 h2 = 7 Do SelectedItemNumber = SelectedItemNumber + 1 With Application.FileDialog(msoFileDialogOpen) .Filters.Clear .Filters.Add "Command Separated Values", "*.csv", 1 '.Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2 '.Filters.Add "Excel 2002-03", "*.xls", 3 .AllowMultiSelect = True .Show For SelectedItemNumber = 1 To .SelectedItems.Count If .SelectedItems.Count > 0 Then Workbooks.Open .SelectedItems(SelectedItemNumber) Set wkbSourceBook = ActiveWorkbook Set rngSourceRange1 = ActiveCell.Offset(1, 0) Set rngSourceRange2 = ActiveCell.Offset(1, 6) For counter = 0 To 300 Columns("H:H").NumberFormat = "0.00" 'Highest = Application.WorksheetFunction.Max(Range("H1:H300")) If Highest <= ActiveCell.Offset(h1, h2).Value Then Highest = ActiveCell.Offset(h1, h2).Value End If h1 = h1 + 1 Next wkbCrntWorkBook.Activate Set rngDestination1 = ActiveCell.Offset(1, 0) Set rngDestination2 = ActiveCell.Offset(1, 1) ActiveCell.Offset(1, 2).Value = Highest For intColumnCount = 1 To rngSourceRange1.Columns.Count If intColumnCount = 1 Then rngSourceRange1.Columns(intColumnCount).Copy rngDestination1 Else rngSourceRange1.Columns(intColumnCount).Copy rngDestination1.End(xlDown).End(xlDown).End(xlUp).Offset(1) End If Next For intColumnCount = 1
in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing http://forum.chandoo.org/threads/vba-runtime-error-6-overflow-solved.19514/ Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings. Yours, Chandoo Hi All Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam Post Spam and you Will Be Deleted as a User Hui... When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post. Welcome runtime error Message to New users Spam Sample File VBA: Runtime error '6' Overflow [SOLVED] Discussion in 'VBA Macros' started by inddon, Oct 3, 2014. inddon Member Messages: 293 Hello There, I have a table in a worksheet and have defined the below procedure. When I select the entire worksheet, I get the error message 'Overflow'. When I select multiple rows, it works fine. Could you please advise, how to overcome the above error? Thanks & regards Don runtime error 6 Code (vb): Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tb As ListObject, CBuyerName As String, CBuyerVATNumber As String 'On Error Resume Next If Target.Count > 1 Then Exit Sub Target.Calculate Application.ScreenUpdating = False Application.EnableEvents = False 'Cells.RowHeight = 15 If Not Intersect(Target, Range("WRMWire")) Is Nothing Then Range(Range("z1").Value).RowHeight = Range("z2").Value Range("Z2") = Target.RowHeight Range("Z1") = Target.Address Target.RowHeight = 25 'Make current row font size 11 'Rows(1).Font.Size = 10.5 End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub inddon, Oct 3, 2014 #1 Hui Excel Ninja Staff Member Messages: 9,837 Don When selecting the whole worksheet, You are selecting 17,179,869,184 cells The count property of a range is probably a Long data type and is hence limited to numbers up to 2,147,483,647 which equates to 131,000 rows I'd change your code as per below to address the issue Old Code Code (vb): 'On Error Resume Next If Target.Count > 1 Then Exit Sub New Code Code (vb): 'On Error Resume Next If Target.Address = "$1:$1048576" Then Exit Sub If Target.Count > 1 Then Exit Sub or Code (vb): 'On Error Resume Next If Target.Rows.Count > 131000 Then Exit Sub If Target.Count > 1 Then Exit Sub Hui, Oct 4, 2014 #2 Marc L Excel Ninja Messages: 2,275 Hi, since Excel 2007, CountLarge propert