Autofilling multiple columns using a variable

[viggie79]

I'm completely befuddled on what seems like it should be an incredibly easy macro. (Only been working with excel a few days, so please don't hate me because I don't know what Im doing yet.)

I'm getting different worksheets in that I plug in formulas across a few rows and then pull them down to the end of the sheet.
I just cant automate the pulldown part. I'm able to highlight the range I need each time using

Range("H2").Select
Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select

and I think I can save this variable range as a variable using

Dim x As Range Set x = Selection

all of this seems to work until I try to autofill using

x.AutoFill Type:=xlFillDefault


Please help! Any explanation you can give me on line by line code would be greatly appreciated as it helps me understand what the hell I was doing wrong and why as well as whats going on.
(I also know the selection methods Im using are not ideal, but they make sense in my head so its easier for me to work with them and Im not hampered by their lack of efficiency or malleability at this point)
Thanks again!

 

[My Aswer Is This]

Do not totally understand your situation but this script will fill down the formula in cell "A1" to the last filled cell in column "A". Maybe you can modify this to your liking.

Sub Fill_Me_Down()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & Lastrow).FillDown
Application.ScreenUpdating = True
End Sub

 

[L. Howard]

Hi viggie79,

Welcome to the forum.

Maybe start with something like this.

For the LastRow and LastColumn you need to have data for the code to know where to count to.

Seems your sheets are each different so the H2 hard coded "start" cell may not play well on other sheets each and every time.

Would help if you post a LINK to a workbook with a couple representative sheets with the data you typically have on the sheets when you get them.

You cannot attach a workbook here but you can use one of the link utilities to post a link. I use Drop Box, but there are others.

Howard

Option Explicit

Sub WhatRng()
Dim DataRange As Range
Dim sht As Worksheet
Dim LastColumn As Long
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet4")

LastColumn = sht.Range("H2").CurrentRegion.Columns.Count
LastRow = sht.Range("H2").CurrentRegion.Rows.Count

MsgBox LastRow & " " & LastColumn

Set DataRange = Range("H2").Resize(LastRow, LastColumn)


  With DataRange
    .Formula = "=A2+B2": .Value = .Value
  End With


End Sub

 

[viggie79]

Sorry I wasn't more clear with what I was trying to accomplish. Im sure this isnt the best way to approach the problem I have but I want to make it work in this way (if possible) so I can try to learn whats going on with the code.

I have multiple sheets with identical columns but a variable amount of rows. Underneath the header of the last few columns I have some formulas that get pasted in by a macro. The formulas are something like add A2+B2, so I need the next one down to be add A3+B3 and so on.

Manually I can drag and highlight the formula cells, and then pull down so that they get copied down in a series by dragging down on the bottom right corner when the + symbol appears.

I can record this as a macro but each sheet I get has a different amount of rows, so it doesnt work for me. Thats why I tried highlighting them by using the ctrl-shift-right arrow and then ctrl-****-down arrow.


I can get the highlighted cells I want on each particular file Im working with, but I cant figure out how to autofill those highlighted cells by 'pulling down' the formulas.

Alternatively, once I highlight the formulas at H2-02, is there something I can use to drag-fill them down to the last used row?



(It occured to me at some point that I can just keep pasting in values via the macro and progressively altering the cells they refer to, but now Im just stubbornly stuck trying to figure out if it can be done this way)

Also, any kind of comprehensive ground up tutorial you guys can recommend will be very helpful. Ive just been googling problems as they come up, but it would be nice to learn the stuff from the ground up instead of trying to decipher code with no reference point.

 

[L. Howard]

The code I gave you puts the value of the formula in the cell, instead of the formula.

Do this to and see if it does what you are shooting for.

A1 and down enter 1 to 10.
B1 and down enter 1 to 10

Then run this macro. Inspect the formulas in columns C, D E.

You should see they auto fill across columns and down rows.

If you want just those VALUES and NOT the formulas, then remove the ' from this line:

.Formula = "=(a1+b1)" ': .Value = .Value

to this

.Formula = "=(a1+b1)": .Value = .Value

You can use the $$'s to hold a row and advance the columns, or hold a column and advance the rows etc.

.Formula = "=(a$1+b$1)": .Value = .Value

.Formula = "=($a1+$b1)": .Value = .Value

Howard

Option Explicit

Sub TesterFormula()

  Dim lRow As Long
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  MsgBox lRow
  With Range("C1").Resize(lRow, 3)
    .Formula = "=(a1+b1)"  ': .Value = .Value
  End With
  
End Sub

 

[viggie79]

hahahahaha!!!!! Thank you!!!! I just messed around with some of the values and made it fit what I had. It works perfectly, thanks so much!


Couple of questions if you still have time.
I understand the first half completely.

.Formula
seems to just point out that you are subbing in a formula instead of text - I assume the With allows you to call multiple methods on Range correct?

I dont really understand what .Resize is doing. I know its affecting the size or the range because I had to change the 3 to a 1 and use the same method for each of my formula columns.

My main question is what instruction are you giving that is causing the fill series to happen? I just don't see any code that looks like its doing an incremental increase on anything.

Thanks again for your help, you made my Friday a lot better!

 

[L. Howard]

For the resize:

Google - excel vba RESIZE

Resizes the specified range. Returns a Range object that represents the resized range.

Syntax expression .Resize(RowSize, ColumnSize)

expression An expression that returns a Range object.

The WITH thing...

seems to just point out that you are subbing in a formula instead of text - I assume the With allows you to call multiple methods on Range correct?

Yes,

With this range
.do this
.do that
.do lots of things
End with


Run this for a demo. Note there is text and note the double quotes. If you had an IF formula to return a value [100] if true and nothing [""] if false, then the "" would need to be """" .

Sub TesterFormula()
  Dim lRow As Long
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  'MsgBox lRow
  
  With Range("C1").Resize(lRow, 3)
    .Formula = "=($a1+$b1 & "" Lots of red!!!"")": .Value = .Value
    .Interior.ColorIndex = 3
  End With
  
End Sub

My main question is what instruction are you giving that is causing the fill series to happen? I just don't see any code that looks like its doing an incremental increase on anything.

The fill is the DUTY of Excel, which it preforms on its own. If you DON'T want to increment, then that's where the use of the absolute relativity comes into play by using $$'s where needed.

Howard

 

[viggie79]

Thanks again! Starting to make sense now.

 

[L. Howard]

You are welcome, and we must not forget that you can use the OFFSET function in there also, as seen in red.

Howard

Sub TesterFormula()
  Dim lRow As Long
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  'MsgBox lRow
  
  With Range("C1").[COLOR="#FF0000"]Offset(5,10).[/COLOR]Resize(lRow, 3)
    .Formula = "=($a1+$b1 & "" Lots of red!!!"")": .Value = .Value
    .Interior.ColorIndex = 3
  End With
  
End Sub