Run-time error '6': Overflow

I

im2bz2p345

Board Regular
Joined
Mar 31, 2008
Messages
229
Hi guys,

I keep getting the error Run-time error '6': Overflow when I try to run my macro. My macro basically opens up a source file and I want it to calculate a value based on a formula (C77/C79*C80), but I keep getting an error. Could anyone help with this?

Rich (BB code): 
For Each Dn In Rng
    If InStr(SourceFile, Dn.Value) > 0 Then
 
    Filematched = True
    
    num = Dn.Row
 
    Workbooks.Open (SourceFile)
    Set XLSFile = ActiveWorkbook
 
    MsgBox (ActiveWorkbook.Name)
    
    ' -- Do your stuff here--.
    
   CalcValue = Range("C77").Value / Range("C79").Value * Range("C80").Value
   ThisWorkbook.Sheets("All Regions_Detail").Range("AL" & num) = CalcValue
 
   End If

When I step through the code, I get the error at the line in red above.

Appreciate any help,

~ Im2bz2p345 :)
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Have you checked the values in the ranges you reference in the calculation?

This might help checking that:
Code: 
a = Range("C77").Value
b = Range("C79").Value 
c = Range("C80").Value
 
MsgBox "a=" & a & " " & "b=" & b & " " & "c=" & c & " "
 
Upvote 0
Thanks Noorie!

That's precisely what's throwing the error code at me.

Is it possible to alter the macro code, so that instead of defining a range [such as Range("C77").Value], I can perform a lookup?

For example in Column B of the source file, I would like it to lookup the string "Pre-tax Cash Flow" and determine what row that is on. Then get the value from Column C and the row that was determined.

I am very new to VB coding, so I don't know exactly how the syntax is written. Any help with creating this type of loop would be appreciated!

Thanks,

~ Im2bz2p345 :)
 
Upvote 0
That could be possible but we would need more information on what you are actually trying to do.

Mind you, before you starting on that it might worth checking some other things.

The main thing I noticed is that you don't have workbook or worksheet references when you use Range.

That could mean the code is trying to get the values from the wrong workbook and/or wrong worksheet.

If you add references for the ranges then it might help ensure the code is looking in the right place for the values.

Perhaps something along the lines of this rough code.:)
Code: 
Dim XLSFile As Workbook
Dim ThisFile As Workbook
 
    ' first set reference to the workbook the code is in
    Set ThisFile = ThisWorkbook
 
    For Each Dn In Rng
 
        If InStr(SourceFile, Dn.Value) > 0 Then
 
            Filematched = True
 
            num = Dn.Row
 
            ' open workbook and create reference to it
            Set XLSFile = Workbooks.Open(SourceFile)
 
            MsgBox (XLSFile.Name)

            ' -- Do your stuff here, using the workbook references XLSFile to refer to the opened workbook
            ' -- and ThisFile to refer to the workbook the code is in
 
            a = XLSFile.Worksheets("Sheet1").Range("C77").Value
 
            b = XLSFile.Worksheets("Sheet1").Range("C79").Value
 
            c = XLSFile.Worksheets("Sheet1").Range("C80").Value
 
            MsgBox "a=" & a & " " & "b=" & b & " " & "c=" & c & " "
 
            CalcValue = a / b * c
 
            ThisFile.Sheets("All Regions_Detail").Range("AL" & num) = CalcValue
            
        End If
 
    Next Dn
 
Upvote 0
Hi Noorie,

My original problem was here: http://www.mrexcel.com/forum/showthread.php?p=2680137

I've been trying to come up with a solution using daverunt's code (in the last post) as a basis.

The problem that I seem to face is that, I cannot just set the range as C77, C79, and C80. They change based on the source file that is being used.

Here is an example of one of my source files: http://ploader.net/files/c6a256d09b0faebb5b07173b48487bee.png

Essentially what I need to get the first value a lookup of the string "Pre-tax Cash Flow" and determine what row that is on. Then get the value from Column C and the row that was determined from the string.

For the second value, I need to lookup the row where it says "S0998" in column D, then get the value in C with that corresponding row.

The third value is always going to be 1 row below the second value, so maybe an offset can be used here?

Finally, what I need done is 1st value/2nd value * 3rd value

Hope that helps clarify what I'm after,

~ Im2bz2p345 :)
 
Upvote 0
Here is what I have so far, hopefully it helps you figure out what I'm trying to do better:

Code: 
Sub Macro1()
Dim XLSFile As Workbook
Dim ThisFile As Workbook
Dim LR As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
'Display Open Dialog to select file directory
  filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select BU Files", "Open", False)
 
'If the user cancels file selection then exit
     If TypeName(filenames) = "Boolean" Then
        Exit Sub
     End If
 
'Set xls as SourceFile
        SourceFile = Dir("*.xls")
 
     Do While SourceFile <> ""
 
   ' first set reference to the workbook the code is in
    Set ThisFile = ThisWorkbook
 
    For Each Dn In Rng
 
        If InStr(SourceFile, Dn.Value) > 0 Then
 
            Filematched = True
 
            num = Dn.Row
 
            ' open workbook and create reference to it
            Set XLSFile = Workbooks.Open(SourceFile)
 
            MsgBox (XLSFile.Name)
            ' -- Do your stuff here, using the workbook references XLSFile to refer to the opened workbook
            ' -- and ThisFile to refer to the workbook the code is in
            
            target = "Pre-tax Cash Flow"
    LR = Range("B" & Rows.Count).End(xlUp).Row
'Find the row the business unit appears on.
    num = Application.Match("*" & target & "*", Range("B1:B" & LR), 0)
'If no match is found with wildcards, the next match will search for when the business unit is the only thing in the cell.
    If IsError(num) Then
        num = Application.Match(target, Range("B1:B" & LR), 0)
    End If
'If the unit is found, continue with code.  If the unit is not found, display a message box.
    If Not IsError(num) Then
 
            a = XLSFile.Range("C" & num).Value
            
            b = XLSFile.Range("C79").Value
 
            c = XLSFile.Range("C80").Value
 
            MsgBox "a=" & a & " " & "b=" & b & " " & "c=" & c & " "
 
            CalcValue = a / b * c
 
            ThisFile.Sheets("All Regions_Detail").Range("AL" & num) = CalcValue
            
        End If
    Next Dn
 Windows.Application.CutCopyMode = False
    If Filematched = True Then XLSFile.Close False
   SourceFile = Dir
  Filematched = False
  Loop
 
End Sub


I am just testing for the first value ("a") right now, so ignore anything related to "b" and "c". I have to remove the worksheet name from your code Noorie, because the source files don't contain the exact same worksheet names.

Currently I am getting a "Compile Error: Next without For." It points to the line "Next Dn" in my code.

Please help if you can!

~ Im2bz2p345 :)
 
Upvote 0
Hi, if anyone could offer some assistance on this, that would be great.

I am still getting a "Compile Error: Next without For," which points to the line "Next Dn" in my code.

Here is my updated code (changed the comments to make sense/added an offset to get the correct value).

Code: 
Sub Macro1()
Dim XLSFile As Workbook
Dim ThisFile As Workbook
Dim LR As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
'Display Open Dialog to select file directory
  filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select BU Files", "Open", False)
 
'If the user cancels file selection then exit
     If TypeName(filenames) = "Boolean" Then
        Exit Sub
     End If
 
'Set xls as SourceFile
        SourceFile = Dir("*.xls")
 
     Do While SourceFile <> ""
 
   ' first set reference to the workbook the code is in
    Set ThisFile = ThisWorkbook
 
    For Each Dn In Rng
 
        If InStr(SourceFile, Dn.Value) > 0 Then
 
            Filematched = True
 
            num = Dn.Row
 
            ' open workbook and create reference to it
            Set XLSFile = Workbooks.Open(SourceFile)
 
            MsgBox (XLSFile.Name)
            ' -- Do your stuff here, using the workbook references XLSFile to refer to the opened workbook
            ' -- and ThisFile to refer to the workbook the code is in
            
            target = "Pre-tax Cash Flow"
    LR = Range("B" & Rows.Count).End(xlUp).Row
'Find the row the string "Pre-tax Cash Flow" appears on.
    num = Application.Match("*" & target & "*", Range("B1:B" & LR), 0)
'If no match is found with wildcards, the next match will search for when the string "Pre-tax Cash Flow" is the only thing in the cell.
    If IsError(num) Then
        num = Application.Match(target, Range("B1:B" & LR), 0)
    End If
'If the string is found, continue with code.  If the string is not found, display a message box.
    If Not IsError(num) Then
 
            a = XLSFile.Range("C" & num).Offset(0, 1).Value
            
            b = XLSFile.Range("C79").Value
 
            c = XLSFile.Range("C80").Value
 
            MsgBox "a=" & a & " " & "b=" & b & " " & "c=" & c & " "
 
            CalcValue = a / b * c
 
            ThisFile.Sheets("All Regions_Detail").Range("AL" & num) = CalcValue
            
        End If
    Next Dn
 Windows.Application.CutCopyMode = False
    If Filematched = True Then XLSFile.Close False
   SourceFile = Dir
  Filematched = False
  Loop
 
End Sub

Thanks for any help with cleaning up this code,

~ Im2bz2p345 :)
 
Upvote 0
This isn't quite cleaning the code up, it actually adds code but the code it adds is kind of needed.:)
Code: 
Option Explicit
Sub Macro1()
Dim XLSFile As Workbook
Dim ThisFile As Workbook
Dim rng As Range
Dim dn As Range
Dim SourceFile As String
Dim target As String
Dim LR As Long
Dim num As Long
Dim FileMatched As Boolean
Dim filenames
Dim a, b, c, CalcValue
 
    Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 
    'Display Open Dialog to select file directory
    filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
                                            "*.xls*", 1, "Select BU Files", "Open", False)
 
    'If the user cancels file selection then exit
    If TypeName(filenames) = "Boolean" Then
        Exit Sub
    End If
 
    'Set xls as SourceFile
    SourceFile = Dir("*.xls")
 
    Do While SourceFile <> ""
 
        ' first set reference to the workbook the code is in
        Set ThisFile = ThisWorkbook
 
        For Each dn In rng
 
            If InStr(SourceFile, dn.Value) > 0 Then
 
                FileMatched = True
 
                num = dn.Row
 
                ' open workbook and create reference to it
                Set XLSFile = Workbooks.Open(SourceFile)
 
                MsgBox (XLSFile.Name)

                ' -- Do your stuff here, using the workbook references XLSFile to refer to the opened workbook

                ' -- and ThisFile to refer to the workbook the code is in
                target = "Pre-tax Cash Flow"
 
                LR = Range("B" & Rows.Count).End(xlUp).Row

                'Find the row the string "Pre-tax Cash Flow" appears on.
                num = Application.Match("*" & target & "*", Range("B1:B" & LR), 0)
                'If no match is found with wildcards, the next match will search for when the string "Pre-tax Cash Flow" is the only thing in the cell.
                If IsError(num) Then
                    num = Application.Match(target, Range("B1:B" & LR), 0)
                End If
 
                'If the string is found, continue with code.  If the string is not found, display a message box.
                If Not IsError(num) Then
 
                    a = XLSFile.Range("C" & num).Offset(0, 1).Value
 
                    b = XLSFile.Range("C79").Value
 
                    c = XLSFile.Range("C80").Value
 
                    MsgBox "a=" & a & " " & "b=" & b & " " & "c=" & c & " "
 
                    CalcValue = a / b * c
 
                    ThisFile.Sheets("All Regions_Detail").Range("AL" & num) = CalcValue
 
                End If

            End If
 
        Next dn
 
        Application.CutCopyMode = False
 
        If FileMatched = True Then XLSFile.Close False
 
        SourceFile = Dir
 
        FileMatched = False
 
    Loop
 
End Sub
It will compile an run but it might not work as required.

One reason for that is because there are still unqualified references like Range here.
Code: 
num = Application.Match("*" & target & "*", Range("B1:B" & LR), 0)
Without specifying the worksheet/workbook you want to use in the Match function VBA might look at the wrong one.

By the way, have a look at using the VBA method Find instead of MATCH.

A good way to get the syntax for that would be to turn on the macro recorder and do the search for 'Pre-tax Cash Flow' manually.

Actually do that a few times, perhaps with different search settings and have a look at the code generated.
 
Upvote 0
Norie said:
One reason for that is because there are still unqualified references like Range here.
Code: 
num = Application.Match("*" & target & "*", Range("B1:B" & LR), 0)
Without specifying the worksheet/workbook you want to use in the Match function VBA might look at the wrong one.
Click to expand...

I updated my code with workbook & worksheet references, but I'm not 100% if they in the correct syntax. Can you please look over them?

Code: 
Option Explicit
Sub Macro1()
Dim XLSFile As Workbook
Dim ThisFile As Workbook
Dim rng As Range
Dim dn As Range
Dim SourceFile As String
Dim target As String
Dim LR As Long
Dim num As Long
Dim FileMatched As Boolean
Dim filenames
Dim a, b, c, CalcValue
 
    Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 
    'Display Open Dialog to select file directory
    filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
                                            "*.xls*", 1, "Select BU Files", "Open", False)
 
    'If the user cancels file selection then exit
    If TypeName(filenames) = "Boolean" Then
        Exit Sub
    End If
 
    'Set xls as SourceFile
    SourceFile = Dir("*.xls")
 
    Do While SourceFile <> ""
 
        ' first set reference to the workbook the code is in
        Set ThisFile = ThisWorkbook
 
        For Each dn In rng
 
            If InStr(SourceFile, dn.Value) > 0 Then
 
                FileMatched = True
 
                num = dn.Row
 
                ' open workbook and create reference to it
                Set XLSFile = Workbooks.Open(SourceFile)
 
                MsgBox (XLSFile.Name)
                ' -- Do your stuff here, using the workbook references XLSFile to refer to the opened workbook
                ' -- and ThisFile to refer to the workbook the code is in
                target = "Pre-tax Cash Flow"
 
                LR = XLSFile.Sheets("ROIC CALC").Range("B" & Rows.Count).End(xlUp).Row
                'Find the row the string "Pre-tax Cash Flow" appears in the source file.
                num = Application.Match("*" & target & "*", XLSFile.Sheets("ROIC CALC").Range("B1:B" & LR), 0)
                'If no match is found with wildcards, the next match will search in the source file for when the
                'string "Pre-tax Cash Flow" is the only thing in the cell.
                If IsError(num) Then
                    num = Application.Match(target, XLSFile.Sheets("ROIC CALC").Range("B1:B" & LR), 0)
                End If
 
                'If the string is found, continue with code.  If the string is not found, display a message box.
                If Not IsError(num) Then
 
                    a = XLSFile.Sheets("ROIC CALC").Range("C" & num).Offset(0, 1).Value
 
                    b = XLSFile.Range("C79").Value
 
                    c = XLSFile.Range("C80").Value
 
                    MsgBox "a=" & a & " " & "b=" & b & " " & "c=" & c & " "
 
                    CalcValue = a / b * c
 
                    ThisFile.Sheets("All Regions_Detail").Range("AL" & num) = CalcValue
 
                End If
            End If
 
        Next dn
 
        Application.CutCopyMode = False
 
        If FileMatched = True Then XLSFile.Close False
 
        SourceFile = Dir
 
        FileMatched = False
 
    Loop
 
 End Sub

I get the following error: Run-time error '13': Type mismatch. When I debug it, it points to the line:
Code: 
num = Application.Match("*" & target & "*", XLSFile.Sheets("ROIC CALC").Range("B1:B" & LR), 0)

Please help!

~ Im2bz2p345 :)
 
Upvote 0
The most likely reason for that error is that Match is not finding a match and returning an error.
That's one of the reasons I suggested you have a look at using VBA Find.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA - Runtime Error 6 - Overflow
Replies
5
Views
369
ststern45
S
S
  • Question Question
How can i prevent overflow in this case
Replies
3
Views
341
Micron
M
K
  • Question Question
Run-time error '-2147217900 (80040e14)': Syntax error or access violation
Replies
0
Views
120
kristianb63
K
M
  • Question Question
Run time error 1004: Application defined or object defined error
Replies
1
Views
564
Micron
M
L
  • Question Question
VBA - Run time error '9': subscript out of range
Replies
3
Views
262
RoryA
RoryA

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

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
Back
Top