SOLVED

Use VBA to Autofill a Row until the end of the number of data in another row

Copper Contributor

Hello,

 

I need some help with the following problem:

 

The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that contain data in the cell next to it (Column E). Column E is the cell that the vlookup refers to.

 

The situation looks like this:

 

2017-12-14_09h25_21.png

 The code that I have so far looks like this:


    Sheets(3).Select
    Range("AY2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],DennisAR!C[-50],1,0)"
    Selection.AutoFill Destination:=Range("AY2:AY1662")
    Range("AY2:AY1662").Select

 

The problem with this is, that the number of rows with data always change every week. So I cannot use a static row number of 1662.

 

I am looking for a way to make Destination:=Range("AY:AY1662) dynamic. In fact it has to refer to the number of rows with data in column E.

 

Thank you very much in advance,

Kai
   

 

 

81 Replies
Hi,
Please can you help, I’m trying to run a macro so it drags down the formula to the last row and it keeps coming up as error


Range("C5,L5").Select
Selection.AutoFill Destination:=Range("C5:C" & Range("B" & Rows.count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

@Scottmonk

 

Try this one instead:

Range("C5").AutoFill Destination:=Range("C5:C" & Range("B" & Rows.Count).End(xlUp).Row)
Range("L5").AutoFill Destination:=Range("L5:L" & Range("B" & Rows.Count).End(xlUp).Row)

@Haytham Amairah 

 

Hello Haytham,

 

My code is erroring out at the last statement. any ideas ?

 

ActiveCell.FormulaR1C1 = _
        "=IF('SD_L1 Calls by Inquiry Type'!RC[-1]=RC[-1],""Match"",""Possible cell shift or new department added"")"
ActiveSheet.Range("B4").Select

Selection.AutoFill Destination:=Range("B4:B255")
   

Hi@Haytham Amairah,

 

I've got the following VBA copying down the =left formula in G down to the last row in column F. But now I want column F to be A on Sheet="Step 2":

 

'Copy above formula to last row
Selection.AutoFill Destination:=Range("G3:A" & Range("F" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

 

The only thing is that I now want the colum that was "F" to now be "A" on sheet "Step 2"

 

How to I update the above?

 

Apreciate your help!

 

Thanks,

Duan

Hello @Haytham Amairah !

I've test your Solution in two WorkBook (A Test and Original) but it only work in the TestBook (it don't use a "Fixed Column" [Copy-Pasted in Other Column and Deleted Duplicates Values]).

I'll Post a CodeExample and MyProblem.

 

*Code Example:

Range("N11").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C[-13]:C[-5],3,FALSE)"

    Range("N11").Select

    Selection.AutoFill Destination:=Range("N11:N" & Range(“M” & Rows.Count).End(xlUp).Row)

   Range(Selection, Selection.End(xlDown)).Select

 

*My Problem:

1.AutoFill Always stops at 35 rows.

2.It work only in Column L [List Column: 1,2,3.....] but doens't work in Columns like N [Formulated: VLOOKUP()]

3. Column J and M are the same, but M is in the table [M11:M] and J is in the DataBase [Same Sheet].

4. If I use "J" It always stops 5 rows before the maximum [Exm: If J has 50 Row, AutoFill Stop in 45].

 

Do you have any clue of this problem? (I can put All Code if Want or WorkBook, but It'll be more long).

I've done it!

'Copy above formula to last row of Step 2 sheet
Selection.AutoFill Destination:=Range("A3:A" & Worksheets("Step 2").Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Regards,
Duan

@Moses76

 

Hi,

 

Sorry for the late reply!

It works without error in my own workbook.

Could you provide a screenshot of the error and the worksheet?

@Duan_90

 

Hi,

 

Sorry for the late reply!

Glad to hear that you did that.

 

Regards

@Mauriq25

 

Hi,

 

I think there are some issues in your original workbook!

If you can attach it or a sample of it, this will be helpful.

@Haytham Amairah 

 

It's a Photo of my Problem:  

 

And there would attach a DataExample (February) and the Code in other Sheet.

I've test February in the Original Excel, in other Excel and in the Test Excel and it don't work :S.

 

Thanks by your time.

@Mauriq25

 

Hi,

 

After inspecting the code, I found some lines that delete the last records!

Clear Contents Bug.png

 

However, I've turned them to comments in the code.

 

Hope that helps

@Haytham Amairah 

 

Hello,

 

Can I say that I love you?

Thanks bro!!! I never find that there was some "Deleting Codes", I've made it by macros and then Editing hehehe.

I'll still working in it and somes futures dudes I'll tell you ;).

Have a nice day.

Hi ,
I have a situation where I have to autofill towards the right of like consider a cell E4 i have to autofill towards the right of it till it's below row has data.

@Haytham Amairah 

Haytham, thank you for this code.

Question - I'm in the process of writing a macro in VB and I need this code to copy a vlookup string value from cell AU2 and keep pasting it in the cell directly below it until reaching a cell with data. I'd then like it to skip that non blank cell(s) and continue pasting into blank cells in this range until reaching the last row. When deciding to "paste" the macro should check Column A row by row and when there is no more data in Column A - the macro should end.  

 

How can I express this logic?

@Thomas2170

 

Hi,

 

Please try this code:

Sub AutoFillToRight()
    Dim lastCellFromRight As String
    ActiveCell.Select

    lastCellFromRight = Selection.End(xlDown).End(xlToRight).Offset(-1, 0).Address
    
    If lastCellFromRight = "$XFD$1048575" Or lastCellFromRight = "$IV$65535" Then Exit Sub
    
    Range(ActiveCell.Address, lastCellFromRight).FillRight
    Range(lastCellFromRight).Select
End Sub

 

But you need to select the starting cell before you run it which in the example below cell (E3):

image.png

 

Hope that helps

@spclfx

 

Hi,

 

Try this code:

 

 

Sub FillOnlyBlankCellsWithFormula()
    Range("AU2:AU10000").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Formula = "=VLOOKUP(B1,$D$9:$E$11,2,0)"
    Range("AU2").Select
End Sub

 

 

 

But change the formula to match your existing data.

 

@Haytham Amairah 

Thank you Haytham! Your code worked but I mis-explained something. My first set of instructions pastes a vlookup string in all the cells in Range AU. When your code runs, it "sees" these values and considers the cell to be non blank so nothing happens.

 

Here's my problem - I need to check a column for a set of about 10 Group tags and then specify which owner should be assigned to each tag in column AU. When I built this macro below, I inputted one vlookup that accurately searches but it only gives me one owner and the same value is pasted down the whole range.

 

For the remaining 9 tags and owners, I need to build logic to recurse through a set of tag names so that I can identify the owner in column AU.

Below is my code... what are your thoughts?


'***************************************************************************************************
'This section will lookup owner names against the tags specified in the column called "OWNERS".

Application.Goto Reference:="R2C47"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "LOOKUP OWNER"
Range("AU2").Select
ActiveWindow.SmallScroll Down:=-9
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""GROUP A"",RC[-1])),""JOHN DOE"","""")"

' ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""GROUP B"",RC[-1])),""LITTLE TIMMY"","""")"
'***************************************************************************************************
'This code copies the above VLookup Statement down the entire Range until the last row containing data. This is using column AT as the indicator for more or end of data.

 

Selection.AutoFill Destination:=Range("AU2:AU" & Range("AT" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
'***************************************************************************************************

' This section will only copy code into blank cells in range AU
'This failed because the cells in range AU have the vlookup string from the previous section.

'FillOnlyBlankCellsWithFormula()
Range("AU2:AU1000").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Formula = "=IF(ISNUMBER(SEARCH(""GROUP B"",RC[-1])),""LITTLE TIMMY"","""")"
Range("AU2").Select
'***************************************************************************************************

 

This is a list of tag values I am searching against. Each group has an owner name that I am manually inputting into the vlookup statement above.

COLUMN "AT" CONTAINS THESE GROUP NAMES

GROUP A
GROUP B
GROUP C
GROUP D

@spclfx

 

Can you attach the worksheet you working on or a sample of it?

So I can reproduce the issue and figure it out.