Method 'Range' of 'object' Global Failed

Question

This used to work then all of a sudden started getting errors:

Method 'Range' of 'object' Global Failed

starting at line with:

Range("G2.G101").Value = "=E2-B2"

What gives?

Sub ReadCSV()
Dim symbol, hi, lo, startdate, enddate, hi2, lo2, hi3, lo3, d5hi, d5lo, d10hi, d10lo, d20hi, d20lo, dayrng As Variant
Dim symbpos As Range
Dim MainWkbk, NextWkbk As Workbook
Set MainWkbk = ActiveWorkbook

'symbol = InputBox("Enter symbol")

symbol = ActiveCell.EntireRow.Cells(1).Value

Workbooks.Open Filename:="f:\downloads\daily_" & symbol & ".csv"
Set NextWkbk = ActiveWorkbook

Range("G1").Value = "Net Ch fr Open"
Range("G2.G101").Value = "=E2-B2"
End Sub

Also, are you trying to put a formula in? If so maybe you want to look into using Formula. Another note, you define workbooks but never use these parent object when you refer to Ranges. — JvdV, Nov 04 '19 at 18:45

Vityata · Accepted Answer · 2019-11-04T19:48:10.720

As mentioned in the comments, Range("G2.G101").Value = "=E2-B2" should be Range(G2:G101).Value. This is easily visible, if the macro recorder is used, just to see how to define the ranges - it works flawlessly there.

Anyway, a few more points:

When the variables are defined like this:

Dim MainWkbk, NextWkbk As Workbook

then the first (MainWkbk) is defined as a Variant and only the latter (NextWkbk) as a workbook. Thus, try to define explicitly - Dim MainWkbk as Workbook, NextWkbk As Workbook.

It is a good idea to avoid at all costs ActiveWorkbook, ActiveCell, etc - How to avoid using Select in Excel VBA. Thus, consider:

Set NextWkbk = Workbooks.Open( Filename:="f:\downloads\daily_" & symbol & ".csv")

Avoiding Range() without a Worksheet. Instead of:

Range("G1").Value = "Net Ch fr Open"
Range("G2:G101").Value = "=E2-B2"

consider:

With NextWkbk.Worksheets(1)
    .Range("G1").Value = "Net Ch fr Open"
    .Range("G2:G101").Formula = "=E2-B2"
End With

because the . before the range specifies the range to the first worksheet of the opened workbook.

Changing .Value to .Formula is the last point, as far as =E2-B2 is a formula and this way it is a bit better, as it improves readability.

Method 'Range' of 'object' Global Failed

1 Answers1