0

I'm looking to drop a formula (column A) to the bottom of a separate range (column H). My data set is very large so want to drop formulas and paste special values over the top of the ranges selected.

Ideally I'd like to create a loop if this is possible so the file doesn't take ages to populate all of the formulas.

Apologies if this has been covered in another post, this is my first time posting. Code below used so far

Sub CC_no()

Sheets("Data").Select

Dim rng As Range, cell As Range
Dim N As Long

N = Cells(Rows.Count, "H").End(xlUp).Row
Set rng = Range("A2:A" & N)
Set cell = Range("H2:H" & N)

For Each cell In rng

Next cell

rng = "=VLOOKUP(H2,'PPD Data'!$K:$AZ,42,0)"

Sheets("Data").Select
Range("A2:A" & N).Select
Application.CutCopyMode = False
Selection.Copy
Range("A2:A" & N).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Call Brand

End Sub
Sub Brand()

Sheets("Data").Select

Dim rng As Range, cell As Range
Dim N As Long

N = Cells(Rows.Count, "H").End(xlUp).Row
Set rng = Range("B2:B" & N)
Set cell = Range("H2:H" & N)

For Each cell In rng

Next cell

rng = "=VLOOKUP(A2,Cost_centre_data!$B:$J,7,0)"

Sheets("Data").Select
Range("B2:B" & N).Select
Application.CutCopyMode = False
Selection.Copy
Range("B2:B" & N).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False


End Sub
G. Parker
  • 11
  • 2
  • 2
    What do you mean by *"drop a formula"*? Copy it down? If yes what have you tried so far? What have you reserached? • Please note that because this is no free code writing service it is necessary that you show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading [ask] might help you to improve your question. – Pᴇʜ May 26 '21 at 09:03
  • Apologies, I wasn't very clear. Yes, I want to copy down the formula (column A) if there's data in the Range (column L). It was more of a question for some advice, this is the code that I have set up for the one formula at the moment but have had to add the end of the range. I would ideally like it to copy down until there is no data in column L. Have edited the post with the current code being used – G. Parker May 26 '21 at 10:55
  • make a table from your data. Formulas will automatically be added as you add data. – ceci May 26 '21 at 10:58

1 Answers1

0

Actually there is no need to copy anything, you just need to write the formula to all cells at once:

Dim ws As Worksheet   ' define your worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim LastRowL As Long   ' find last used row in column L
LastRowL = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row

' write your formula to the entire range Excel adjusts H2 in the formula automatically for each row
ws.Range("A2", "A" & LastRowL).Forumla = "=VLOOKUP(H2,'PPD Data'!$K:$AZ,42,0)"
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thanks, I couldn't get the bottom line of code to work but have managed to copy down formulas for 2 columns. Is there a simple way of writing this into 1 sub rather than calling subs like I have done above? Have edited the post with the working script – G. Parker May 26 '21 at 13:40
  • @G.Parker What I showed is the proper way to go. If you got issues to get it work please tell what exactly happened and **what** did not work? • 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) code using `.Select` is not reliable and might somtimes work and sometimes not. – Pᴇʜ May 27 '21 at 06:16