Application.Goto with active file runtime error

Question

I am trying to run a code that was run by someone else and I am getting a runtime error. In the last two lines

Sub calculateCompositions()

    Set prodProfilesSheet = Workbooks("Production Profiles (revised).xlsb").Sheets("Ref Compr - Wells")

    Set fluidCompSheet = Workbooks("Production Profiles (revised).xlsb").Sheets("Well Base Compositions")

    Set flashCalcSheet = Workbooks("Production Profiles (revised).xlsb").Sheets("Well Base Compositions")

    total_columns = prodProfilesSheet.Range("C1").CurrentRegion.Columns.Count

    c = ActiveCell.CurrentRegion.Column

    numberOfRowsInRegion = ActiveCell.CurrentRegion.Rows.Count
    numberOfheaderRows = 6
    numberOfDataRows = numberOfRowsInRegion
    firstDataRow = ActiveCell.Row

    Do While c < total_columns
    fluidCompSheet.Range("L2").Value = current_well_name
    compositionName = fluidCompSheet.Range("O1").Value
    fluidCompSheet.Range("O3:O32").Copy
    flashCalcSheet.Range("I8").PasteSpecial Paste:=xlPasteValues, 
    Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False   
    If current_well_name = "Existing wells" Then 
    Exit Do
    For r = firstDataRow To firstDataRow + numberOfDataRows

  prodProfilesSheet.Cells(r - 5, c).Activate
  Application.Goto Reference:=Active, Scroll:=True

  '...
end sub

If I don't activate the file and put the cells inside the application Goto like this:

Application.Goto Reference:=prodProfilesSheet.Cells(r - 5, c), Scroll:=True

The code runs but does not do what it supposed to (which is copy the data from the main file to a secondary one, run the calculate composition sub and paste the resulting data back to the main file) . Since, I did not write the code, I am not really sure what is happening in those two lines.

Any ideas on how to get out of it?

Thanks

What *is* `Active`? Unless this is a range variable that has been assigned properly, I think you'll get a 424 Object Required error. In the other case: "The code runs but doesnot do what it supposed to", perhaps you can explain what the code *is* supposed to do, and for that you'll need to show more of your code. Please see [ask] and include enough information to comprise a [mcve]. — David Zemens, Jul 06 '18 at 12:34
It sounds like the full code you have doesn't work (in the sense that it "does not do what it's supposed to do"). You're going to need to provide more information, otherwise this question should be closed as *unclear what you're asking*. — David Zemens, Jul 06 '18 at 12:42
I just added more of the code. It is my first time using stock overflow, so I am trying to figure out how best to ask as well. Is that more clear now? — Gladys, Jul 06 '18 at 13:04
Question looks better now! Thanks for revising :) As for the expected results, you may need to step through the code using F8 key in debug mode, and try to narrow down the source of the discrepancy, but I suspect that the `Do While` loop is not being entered, so no data is copied/pasted. We would need to see some example input data, I think, at minimum in order to advise further. — David Zemens, Jul 06 '18 at 13:11
see [this excellent primer](http://www.cpearson.com/excel/debuggingvba.aspx) for some tips on debugging VBA. — David Zemens, Jul 06 '18 at 13:12

score 1 · Answer 1 · 2018-07-06T13:04:00.910

1

tldr: Get rid of the .Activate operation and just use Application.GoTo.

It works with,

Application.Goto Reference:=prodProfilesSheet.Cells(r - 5, c), Scroll:=True

... because you can pass the Reference argument to the Application.Goto Method as either an actual range object or a xlR1C1 string and prodProfilesSheet.Cells(r - 5, c) is a range object. To pass the cell reference as a string, use,

dim addr as string
addr = prodProfilesSheet.Cells(r - 5, c).Address(0, 0, ReferenceStyle:=xlR1C1, external:=true)
Application.Goto Reference:=addr, Scroll:=True

You've offered no indication as to what Active represents but I strongly suspect it used to be ActiveCell and the Application.GoTo is used simply to put the cell in the top-left corner of the worksheet window after using Range.Activate Method to focus Selection on that cell. Since Application.GoTo does both of the actions itself, the .Activate operation is redundant and could cause problems if prodProfilesSheet was not the active worksheet.

In other words, Application.GoTo can move to and activate a cell on the current worksheet or another worksheet but .Activate can only bring focus to a cell on the current worksheet. Further, .Activate needs Application.GoTo to bring the cell to the top-left corner of the worksheet window while Application.GoTo by its nature needs nothing else when the Scroll:=True argument is applied.

edited Jul 06 '18 at 13:04

answered Jul 06 '18 at 12:51

Hi Jeeped, thanks a lot for your answer. I was reading the helps for both Activate and GoTo method and had the idea that the Activate line is redundant, but when I took it out the code appeared to run, but the results did not change even when I change part of the data in the main file – Gladys Jul 06 '18 at 13:07
Yes, but you need to get rid of `Active` and replace it with `prodProfilesSheet.Cells(r - 5, c)` in the Ap.GoTo. – Jul 06 '18 at 13:08
You are in a For ... Next loop that uses App.GoTo to move to a cell so some operations can be performed on it. Oddly, you decided that part wasn't important to include but in any event, you are looping through columns (`Do While c < total_columns`) and then looping through rows (`For r = firstDataRow To firstDataRow + numberOfDataRows`) and performing one or more operations. – Jul 06 '18 at 13:11
so then the reason the ActiveCell is required is because it is inside a For... next loop? I used to code much more, but it has been too long. I forgot most of the rules and methods. What is copying the cell, the scroll? I thought GoTo simply selected the cell – Gladys Jul 06 '18 at 13:14
1

@Gladys if you read about the `Application.GoTo` method, the documentation will be clear about what the `Scroll` argument does :) The `GoTo` does essentially select/activate a particular cell, and while it may be necessary in the specific case (i.e., if the objects are not well qualified), [in the general case it is 99.9% never necessary, and should generally be avoided/refactored](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – David Zemens Jul 06 '18 at 13:20
@Gladys - Nothing you've shown is copying the cell. I suppose there is something like ActiveCell.Copy in parts of the code within The For ... Next loop that you have shown us. – Jul 06 '18 at 13:24

Application.Goto with active file runtime error

1 Answers1