PDA

View Full Version : Range cells combination problem



Cavaliere
03-24-2010, 06:04 AM
Hi everyone,

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


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


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

mdmackillop
03-24-2010, 06:20 AM
Can you post a sample of your workbook

Cavaliere
03-24-2010, 06:43 AM
That's my workbook

mdmackillop
03-24-2010, 06:56 AM
Too many "range"s. Also, make sure the range sizes match by using Resize

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



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

Cavaliere
03-24-2010, 07:12 AM
Thanks a lot.