Excel vba autofill coding

Question

I am currently using vba to perform the following tasks. So I copied data from sheet1 and pasted it in sheet2. There was already data in sheet2, so I pasted below the existing data. Now I need to perform a vlookup to get data from sheet3 to sheet2 for certain columns, so the vlookup would send the data below the existing data on sheet2. I have the following codes below

Range(“F1”).End(xldown).Offset(1).Select
ActiveCell.FormulaR1C1 = “=Vlookup(RC[7],Monitor_Report[#All],4,FALSE)”

ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & “:” & ActiveCell.End(xlDown).Offset(-1,0).Address)

So currently even tho, the next row has no data, it still auto fills and results in #N/A after the cell where the vlookup occurs first. i do not want the autofill to happen if the next row has no data. Can anyone assist me to modify this code?

Hi! Have you tried to add a "IFNA" statement to your formula? It handles what happens if value returns #N/A. You could simply have your formula return blank if #N/A. — Havard Kleven
– Havard Kleven, Commented Jun 7, 2022 at 12:49
@HavardKleven thank you for your response, how would I implement that in the code example that I provided? — 0726
– 0726, Commented Jun 7, 2022 at 12:56

Havard Kleven · Accepted Answer · 2022-06-07 13:51:56Z

0

If you're set on using the manual way of .Select and .AutoFill, I'd just add an IFNA-statement to your formula:

Sub test()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Sheet1")

  ws.Range("F1").End(xlDown).Offset(1).Select
    
  Dim fRow As Long

  With ws
    fRow = .Cells(.Rows.Count, ws.ListObjects("Monitor_Report").DataBodyRange.Column).End(xlUp).Row
    fAdr = ActiveCell.Column
  End With

  adr_ = ws.Cells(fRow, fAdr).Address

  ActiveCell.Value = "=IFNA(VLOOKUP(M3,Monitor_Report[#All],4,FALSE),"""")" '
  ActiveCell.AutoFill Destination:=ws.Range(ActiveCell.Address & ":" & adr_)


End Sub

edited Jun 7, 2022 at 13:51

answered Jun 7, 2022 at 12:57

Havard Kleven

4317 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

0726 Over a year ago

thank you. It worked. Is there a way to select only the body range without auto filling for all the cells below to the end of the sheet?

Havard Kleven Over a year ago

Not sure if I understand, but should be possible. You'd need a way to check where your last row is located. Your formula above actually handles that. But instead of ActiveCell.Address, use the last cell/row in your table

Havard Kleven Over a year ago

Edited answer. Please see the edit summary for details on change.

0726 Over a year ago

I declared the last row, Dim LR as Long, LR = Cells(Rows.Count,1).End(xlUp).Row. Then I added it to the autofill formula, ActiveCell.Autofill Destination:=Range(LR & ActiveCell.End(xldown).Offset(-1,0).Address), it didn’t work tho, I got an error. Should I place LR in another place in the coding?

0726 Over a year ago

I got autofill method of range failed for this line “ActiveCell.AutoFill Destinstion:=ws.Range(ActiveCell.Address & “:” & adr_)

|

Collectives™ on Stack Overflow

Excel vba autofill coding

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related