VBA Code error - Range

[Molletica]

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

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

 

[Joe4]

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]

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:

Dim xDRg1 as Range, xDRg2 as Range, xDRg3 as Range, xDRg4 as Range, xDRg5 as Range

or like this:

Dim xDRg1 as Range
Dim xDRg2 as Range
Dim xDRg3 as Range
Dim xDRg4 as Range
Dim xDRg5 as Range

 

[Molletica]

Thank you Got it....

 

[Joe4]

You are welcome.

 

[Molletica]

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:

Dim xDRg1 as Range, xDRg2 as Range, xDRg3 as Range, xDRg4 as Range, xDRg5 as Range

or like this:

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]

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).

 

[Molletica]

Thank you for the good explanation.. will update my code accordingly....

 

[Joe4]

You are welcome.