Object doesn't support this property or method Error

    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!
LVL 13
Shanan212Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
It's R1C1 no R1C2.
Shanan212Author Commented:
Yes, thats the source of error. But then how do I bring the 2nd formula to 'Column B' - 'Row2'?
NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Shanan212Author Commented:
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

Shanan212Author Commented:
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.
NorieAnalyst Assistant Commented:
You shouldn't need to use Activate.
Shanan212Author Commented:
Please re-open this thread so I can award point to the above expert (his method is most correct)
Shanan212Author Commented:
Thanks! I ended up using your solution!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.