1

enter image description hereI have a data file, that I will keep adding new rows to the end of the existing data weekly. For the new rows added, I need to vlookup column A, to fill the Qtr and Year in column "G" & "H" and updated in column "I" with "done" text. I tried following, but it's not dynamic and the vlookup formula and code is not working.

Dim lastA As Integer

Worksheets("Data").Range("A1").AutoFilter

ActiveSheet.Range("A:I").AutoFilter Field:=9, Criteria1:="="

Range("A1").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, -6).PasteSpecial (xlPasteValues)
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,WW!C3:C6,2,0)"
Selection.AutoFill Destination:=Range("G2667:H2667"), Type:=xlFillDefault
lastA = Range("A" & Rows.Count).End(xlUp).Row
lastG = Range("G" & lastA).End(xlUp).Row + 1
value2Paste = "done"
Range("I" & lastI & ":I" & lastA) = value2Paste*

Attached the sample of the file and data

Attached the sample of the file and data

Ann
  • 11
  • 3
  • 2
    Please note that *"is not working"* is no useful error description. Please tell what exactly is going wrong or which errors you get. Also it is unclear what the exact goal is. Reading [ask] and [mcve] might help to improve your question. • And you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jun 09 '20 at 06:04
  • I wanted to fill up the column G & H with dynamic vlookup formula and updated the column I with text "done". This is just for my weekly data compilation to generate a pivot table. It's a personal and not for business use. – Ann Jun 09 '20 at 06:22
  • it showing run time error 438, object doesn't support this property or method. I'm not good in VBA, just half pass through, not sure what is the best and dynamic code to use to have this run smoothly. – Ann Jun 09 '20 at 06:39
  • You didn't show what the lookup should actually lookup. What's the data in WW? – Pᴇʜ Jun 09 '20 at 06:45
  • So sorry, I'm trying to add the excel, but cannot. was trying to vlookup the WW, from column C to F. For Qtr to lookup into Data Column G, and year to column H, let me try to add in the snap shot again. After the empty cells of Data in Column G & H are filled, then will update the column I with done, so that next week, when I have new rows, I will not repeated to lookup, and I can paste the new row at the bottom of the lines. (This, I have no issue to write the code) – Ann Jun 09 '20 at 06:51
  • In which line of code do you get the error? • Note that your formula `=VLOOKUP(RC1,WW!C3:C6,2,0)` does not make and sense because you lookup in range `WW!C3:C6` and want to return column 2 of that range (but it has only 1 column). Furthermore you cannot mix the R1C1 notation and the A1 notation in a formula. Either use A1 notation and `.Formula` or R1C1 notation and `.FormulaR1C1` – Pᴇʜ Jun 09 '20 at 07:16
  • Also what I don't understand is how can the date `6/29/2019` be the first week or the first qtr of 2020? – Pᴇʜ Jun 09 '20 at 07:24
  • Why do you use VBA for this?! Format your Data-Table as Table ([CTRL]+[T]) and write your VLOOKUP in Column G+H. Paste you new Data every week to the end of the table and Excel will do the rest. – Chris Jun 09 '20 at 07:45
  • it's actually a cumulative of few years data, so, it's a huge data. And needed to simplified to review the yearly numbers – Ann Jun 09 '20 at 07:55
  • Hi PEH, thank you so much for hilighting the error, will try and check. – Ann Jun 09 '20 at 07:57

0 Answers0