CODEWITHSUNDEEP
excelvba
Gladys
Gladys

Reputation: 19

Application.Goto with active file runtime error

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

Upvotes: 1

Views: 424

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions