VBA Autofill

[Balmer07]

Hi,

I am trying to create a macro to make my life a lot easier each month.
I download an excel file and need to add to formula's to each row. However the amount of data on the spreadsheet will vary month to month.
So what I need to do is have VBA put the formula in cell S2 and then drag it down to the last cell with data in it then stop. I can't seem to find a straight forward answer on this. Is a loop mybe better than trying to autofill?

Please advise,

 

[footoo]

What is the formula?
What column can be used to determine the last row?

 

[Peter_SSs]

Might depend a bit on what that S2 formula is, but suppose S2 was =N2+2*J2+SUM(P2:R2)
and "last cell with data" can be determined from column A then all you need is

Sub EnterFormula()
  Range("S2:S" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=N2+2*J2+SUM(P2:R2)"
End Sub

 

[Balmer07]

Hi folks,

The formula is an
IF(ISNUMBER(SEARCH(
It applies to the data in Row R

Does this help you?

 

[Peter_SSs]

Hi folks,

The formula is an
IF(ISNUMBER(SEARCH(
It applies to the data in Row R

Does this help you?

Then the structure I gave you should do, except that if the formula contains any double quote marks they need to be doubled up in the formula.
So if S2 was =IF(ISNUMBER(SEARCH("abc",J2)),M2,"Not found")
then the code would be

Sub EnterFormula()
  Range("S2:S" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(ISNUMBER(SEARCH(""abc"",J2)),M2,""Not found"")"
End Sub

 

[Balmer07]

Worked perfectly!!

Thanks

 

[Kamolga]

My favorite approach is to have a table where I import Data (insert/table). If the data is 8 column, I put the formula once in the 9th first row and it will always populate all rows automatically (no code needed, the entire column of the table will be populated).

In VBA I prefer to work with table databodyrange than

[/FONT][/COLOR][COLOR=#333333][FONT=monospace]Range("S2:S" & Range("A" & Rows.Count).End(xlUp).Row)[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]

as well.​

In addition to this, it has the tremendous advantage to have a dynamic range (the table extend automatically when adding rows) I can use for a pivot for example (no need to redo the pivot every time you add a row). Another habit is to have a macro refreshing this pivot when user come on its sheet so anyone can paste data, the formulae and pivots are always up to date.

 

[Peter_SSs]

Worked perfectly!!

Thanks

Cheers. You're welcome.