Method Range of object global failed

Status
Not open for further replies.

mvandeynze

New Member
Joined
Apr 30, 2018
Messages
9
https://www.experts-exchange.com/que...VBA-Excel.html this link is basically what I want to do but I don't think the solution applies here.
I want to select certain ranges to add to a new sheet as a report depending on which checkboxes are selected in the Userform.


Here is my code: (bold is where the code stops)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim RPT As Range

Set RPT = Sheets("CODE").Range("A1")
With Sheets("Calculations")
If ComboBox2.Value = 40 Or ComboBox2.Value = 45 Then
If CheckBox1.Value = True Then
Set RPT = Range(Range("A1129:E1175"), Range(RPT))
Else
Set RPT = RPT
End If
If CheckBox2.Value = True Then
Set RPT = Range(.Range("A1176:E1222"), RPT)
Else
Set RPT = RPT
End If
If CheckBox3.Value = True Then
Set RPT = Range(.Range("A223:E1269"), RPT)
Else
Set RPT = RPT
End If
If CheckBox4.Value = True Then
Set RPT = Range(.Range("A1270:E1316"), RPT)
Else
Set RPT = RPT
End If
If CheckBox5.Value = True Then
Set RPT = Range(.Range("A1411:E1457"), RPT)
Else
Set RPT = RPT
End If
If CheckBox6.Value = True Then
Set RPT = Range(.Range("A1458:E1504"), RPT)
Else
Set RPT = RPT
End If
If CheckBox12.Value = True Then
Set RPT = Range(.Range("A1317:E1363"), RPT)
Else
Set RPT = RPT
End If
If CheckBox7.Value = True Then
Set RPT = Range(.Range("A1364:E1410"), RPT)
Else
Set RPT = RPT
End If
End If
End With
With ThisWorkbook
.Sheets.Add(After:=.Sheets("Report")).Name = "Broker Profitability"
End With

Dim FName As Variant
FName = Application.GetSaveAsFilename( _
InitialFileName:="Broker Profitability.pdf", _
FileFilter:="PDF files, *.pdf", _
Title:="Export to pdf")
If FName <> False Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Else

End If

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
However I have tried multiple ways of setting the range some examples are below (they always start with Set RPT = )
Union(MBHWP, RPT)

Range(MBHWP, RPT)
'with MBWHP being set as the range listed in the bolded line where we get the error message. it is Dim MBWHP as range
Range(Range(MBWHP), RPT)

Range("MBHWP, RPT")

Union("MBHWP, RPT")

Range("A1129:E1175", RPT)

Union("A1129:E1175", RPT)

Range(Range("A1129:E1175"), RPT)

Union(Range(MBHWP), Range(RPT))

Range(Range(MBHWP), Range(RPT))


I've also probably tried more variations, but none come to mind right now.
I get the Method range of object global failed for when the function is range and
the method union of object global failed for when the function is union

I don't think it would be a problem with the rest of my code as it only ever stops at the first range/union line. I have also tried rearranging some of the IF statements in case that was the problem, however it has not fixed anything. I have searched tons of forums and I can't figure out why my code is not working.

I have also tried declaring more RPT variables so there were no repeats in any of the code (ex RPT, RPTA, RPTB, RPTC, RPTD, RPTE, RPTF, RPTG, RPTH, all dim as range) and then having
else
RPTA = RPT
(for example)

Is union even what I want to use or am I better off using range? I have read so many forums I'm confused as to whether or not union actually combines all the ranges together or if it only takes the common cells within the ranges as its range.

For reference all the cells and ranges are on the same sheet that is called "Reports" (I had read that the ranges not being on the same sheet is a problem for union, not that it changed my layout at all)

I am on work day two of trying to figure out this error so any help is GREATLY appreciated.

Thank you.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
Duplicate https://www.mrexcel.com/forum/excel...ges-based-criteria-excel-vba.html#post5060563

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,401
Members
409,871
Latest member
i1patrick
Top