VBA Code error - Range

Molletica

New Member
Joined
Nov 13, 2012
Messages
12
HI Friends,
I tried to create a VBA code to generate List of possible combinations. But when I execute for first time it was successful ( though i tried multiple times). But unfortunately it stopped working stating Error msg as "Wrong number of arguments or invalid property assignments" when I am selecting a range of cells. below is the code:

when i execute the code it stuck at range and showing the error as mentioned above.

i am attaching screen shot of error msg.
Sub consolidate()

Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As range ( when i select Range ( in caps) it is automatically converting in to lower case)
Dim xRg As range
Excel error.png

Dim xStr As String
Dim xFN1, xFN2, xFN3, xFN4, xFN5 As Integer
Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String
Set xDRg1 = range("A2:A6") 'First column data
Set xDRg2 = range("B2:B6") 'Second column data
Set xDRg3 = range("C2:C6") 'Third column data
Set xDRg4 = range("D2:D6") 'Third column data
Set xDRg5 = range("E2:E6") 'Third column data
xStr = "-" 'Separator
Set xRg = range("G2") 'Output cell
For xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Text
For xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text
For xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text
For xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text
For xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xSV4 & xStr & xSV5 & xStr
Set xRg = xRg.Offset(1, 0)
Next
Next
Next
Next
Next
End Sub
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
when i select Range ( in caps) it is automatically converting in to lower case
That sounds like you have either set that variable somewhere else, or you have a Procedure or Function named "range".
You will want to identify and fix that, otherwise you will probably get errors.
Rule of thumb: Never use reserved words (names of existing functions, properties, etc) to name your variables, procedures, or functions!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As range
Also note that the line above only declates "xDRg5" as range. The rest are declared as variant. Each one must be stated explicitly, like this:
VBA Code:
Dim xDRg1 as Range, xDRg2 as Range, xDRg3 as Range, xDRg4 as Range, xDRg5 as Range
or like this:
VBA Code:
Dim xDRg1 as Range
Dim xDRg2 as Range
Dim xDRg3 as Range
Dim xDRg4 as Range
Dim xDRg5 as Range
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome.
 

Molletica

New Member
Joined
Nov 13, 2012
Messages
12
Also note that the line above only declates "xDRg5" as range. The rest are declared as variant. Each one must be stated explicitly, like this:
VBA Code:
Dim xDRg1 as Range, xDRg2 as Range, xDRg3 as Range, xDRg4 as Range, xDRg5 as Range
or like this:
VBA Code:
Dim xDRg1 as Range
Dim xDRg2 as Range
Dim xDRg3 as Range
Dim xDRg4 as Range
Dim xDRg5 as Range
IT is working perfect by declaring the the way i have updated.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

IT is working perfect by declaring the the way i have updated.
It will still work, but it isn't actually doing what you think it is doing.

There are a few reasons to declare variables:
- To couple with the "Option Explicit" command to ensure you only use declared variables (helps identify typos)
- To help ensure only valid data gets entered into the variable (by declaring the specific data type)
- To decrease the amount of memory used (certain data types use more memory)

If you do not declare a variable, it will default to use the "Variant" data type. This data type pretty much accepts everything and anything, and uses the most memory.

Declaring it like you did will set the first four variables as Variant (because none of them has "as Range" after them), and the last one will be declared as Range.
If you want to declare all 5 as Range, you need to do it one of the ways I showed you. Otherwise, the first 4 are treated as Variant data types.

So what you wrote works, but is more prone to errors and uses more memory than if you had declared each variable independently (which kind of defeats the purpose of declaring them in the first place).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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
Top