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?
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
It's R1C1 no R1C2.
0
 
Shanan212Author Commented:
Yes, thats the source of error. But then how do I bring the 2nd formula to 'Column B' - 'Row2'?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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

0
 
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.
0
 
NorieVBA ExpertCommented:
You shouldn't need to use Activate.
0
 
Shanan212Author Commented:
Please re-open this thread so I can award point to the above expert (his method is most correct)
0
 
Shanan212Author Commented:
Thanks! I ended up using your solution!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.