0

I have a complex that is doing alot of copy pasting to different files, however, I am getting an error on the following line and error is object doesn't support this property or method

.Range("Q" & i).Value="if(P" & i & "=" & Chr(34) & "Y" & Chr(34) & ",vlookup(A" & i & "," & OutShVar & "!$A:$BP,68,0)," & Chr(34) & "Not Available" & Chr(34) & ")"

(I am copy pasting parts that are relevant to the question.)

  Set OutShVar=ThisWorkbook.Worksheets("MasterReport")
  Set RngConcat=OutShVar.Range("A:A")

  Set wMain=Workbooks.open(ForePath & sfn)
  Call OpenLink 'Performs tasks on another report after opening it


  'After doing a bunch of things on the OpenLink report,
    'i want to do a vlookup on that report that will take things from the excel
    'workbook where the macro is i.e., OutShVar and RngCon'

   'On Master list/Refresh
   if .Range("P" & i).Value = "N" Then
   .Range("Q" & i).Value="Not inscope"
  Else
   If not IsError(Application.Match(.Range("A" & i).Value,RngConcat,0) Then
    .Range("Q" & i).Value="if(P" & i & "=" & Chr(34) & "Y" & Chr(34) & ",vlookup(A" & i & "," & OutShVar & "!$A:$BP,68,0)," & Chr(34) & "Not Available" & Chr(34) & ")"
    'This is where the problem is, not sure if this is right way to do the vlookup?
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Piccinin
  • 103
  • 7

1 Answers1

1

To assign a formula, use the Formula property of Range Object. Also, use the Worksheet Name property to use it rather than worksheet object in the formula.

The code below should work fine for you.

.Range("Q" & i).Formula = "= IF(P" & i & "=" & Chr(34) & "Y" & Chr(34) & ",VLOOKUP(A" & i & ",'" & OutShVar.Name & "'!$A:$BP,68,0)," & Chr(34) & "Not Available" & Chr(34) & ")"

Below is the difference between key properties of range for future reference:

.Text gives a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####

.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)

.Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.

Source: Another StackOverflow answer

jainashish
  • 4,702
  • 5
  • 37
  • 48