?
Solved

Object doesn't support this property or method Error

Posted on 2011-10-03
8
Medium Priority
?
408 Views
Last Modified: 2012-06-21
    Dim BaseWks As Worksheet, lastrow As Long, n As Integer
    
    Dim wkb As Workbook, rngTarget As Excel.Range

     Set wkb = ThisWorkbook
 
    Set BaseWks = ActiveSheet
    lastrow = BaseWks.UsedRange.Rows.Count - BaseWks.UsedRange.Row + 1
      
    Set rngTarget = Range(BaseWks.Cells(1), BaseWks.Cells(lastrow, 1))
    rngTarget.FormulaR1C1 = "= VLookup(RC[1], 'GL Summary'!Ranges, 2, False)"
    
    Set rngTarget = Range(BaseWks.Cells(1), BaseWks.Cells(lastrow, 1))
    rngTarget.FormulaR1C2 = "= VLookup(RC[2], 'GL Summary'!Ranges, 3, False)"
    
    Range("A1:E1") = Split("Branch, Category, GL ID,Sum of Debt,Sum of Credit", ",")

    BaseWks.Columns.AutoFit

Open in new window


Morning,

I am trying to run the above coding within a function. First column contains the first formula, and second column contains the formula below.

I am getting an error 'Object doesn't support this property or method' on this line

rngTarget.FormulaR1C2 = "= VLookup(RC[2], 'GL Summary'!Ranges, 3, False)"

Sample of data I have is below:

First formula is being used to find out range and 2nd is being used to find out Category column! Vloopup is looking at GL ID as reference.


Branch      Category  GL ID                     Sum of Debt      Sum of Credit      
#N/A            2130-01HST      413.47      0
#N/A            2155-01UNION      0      31704.54
#N/A            2168-01CPP PAY      0      153382.12

I tried all possible combinations but the error wouldn't go away from that line! Any help is much appreciated!

Thanks!
0
Comment
Question by:Shanan212
  • 5
  • 3
8 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 36903963
It's R1C1 no R1C2.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36903976
Yes, thats the source of error. But then how do I bring the 2nd formula to 'Column B' - 'Row2'?
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 36904015
You didn't really ask about that as far as I can see.

Were you trying to refer to another column with R1C2?

If you do want to refer to the column to the right of rngTarget you can use Offset.

Set rngTarget = Range(BaseWks.Cells(1), BaseWks.Cells(lastrow, 1))    

rngTarget.FormulaR1C1 = "= VLookup(RC[1], 'GL Summary'!Ranges, 2, False)"       

rngTarget.Offset(,1).FormulaR1C1 = "= VLookup(RC[2], 'GL Summary'!Ranges, 3, False)"

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Author Comment

by:Shanan212
ID: 36904021
I've got it by doing this!

    rngTarget.FormulaR1C1 = "= VLookup(RC[2], 'GL Summary'!Ranges, 2, False)"
    
    Range("B2").Activate
    'Set rngTarget = Range(BaseWks.Cells(1), BaseWks.Cells(lastrow, 1))
    ActiveCell.FormulaR1C1 = "= VLookup(RC[1], 'GL Summary'!Ranges, 3, False)"
    
   

Open in new window

0
 
LVL 13

Author Comment

by:Shanan212
ID: 36904806
I've requested that this question be closed as follows:

Accepted answer: 0 points for Shanan212's comment http:/Q_27377241.html#36904021

for the following reason:

Thank you for your attempt to help as well. I had to place the 2nd formula (which is same as the 1st except for the last part) in 2nd row.
0
 
LVL 35

Assisted Solution

by:Norie
Norie earned 2000 total points
ID: 36904051
You shouldn't need to use Activate.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36904807
Please re-open this thread so I can award point to the above expert (his method is most correct)
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36904809
Thanks! I ended up using your solution!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question