Range cells combination problem

[Cavaliere]

Hi everyone,

Could you please tell me what's wrong with the following script or suggest alternative and more clear variant

[vba]
Worksheets("DataBases").Activate
l = WorksheetFunction.Match("Test", Range("C2:F2"), 0)
Range("B1:B9").Value = Range(Range(Cells(2, l + 2)).End(xlUp), Range(Cells(2, l + 2)).End(xlDown)).Value
[/vba]

VB gives "Method 'Range' of object '_Global' failed"
Thanks is advance

[mdmackillop]

Can you post a sample of your workbook

[Cavaliere]

That's my workbook

[mdmackillop]

Too many "range"s. Also, make sure the range sizes match by using Resize
[vba]
Private Sub Testing()
Dim Cnt As Variant
Dim Rng As Range
Worksheets("DataBases").Activate
Cnt = WorksheetFunction.Match("Test", Range("C2:F2"), 0)
Set Rng = Range(Cells(2, Cnt + 2).End(xlUp), Cells(2, Cnt + 2).End(xlDown))
Range("B1").Resize(Rng.Cells.Count).Value = Rng.Value
End Sub

[/vba]

I don't like "l" as a variable. I get it confused with "1" in the VBE

[Cavaliere]

Thanks a lot.