-
Range cells combination problem
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
-
Can you post a sample of your workbook
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
-
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules