Dec 14 2017
06:32 AM
- last edited on
Jul 25 2018
09:46 AM
by
TechCommunityAP
Dec 14 2017
06:32 AM
- last edited on
Jul 25 2018
09:46 AM
by
TechCommunityAP
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:
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
Jan 07 2020 02:09 AM - edited Jan 09 2020 01:16 AM
Jan 08 2020 10:12 AM
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)
Jan 09 2020 01:17 AM
Apr 20 2020 11:44 AM
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")
Apr 21 2020 10:22 AM
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
Apr 21 2020 11:47 AM
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).
Apr 22 2020 02:59 AM
Apr 22 2020 08:13 AM
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?
Apr 22 2020 08:19 AM
Apr 22 2020 11:21 PM
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.
Apr 23 2020 06:58 AM
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.
Apr 23 2020 07:33 AM
Hi,
After inspecting the code, I found some lines that delete the last records!
However, I've turned them to comments in the code.
Hope that helps
Apr 23 2020 08:01 AM
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.
Apr 23 2020 08:23 AM
May 11 2020 05:49 AM
May 13 2020 10:37 AM
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?
May 13 2020 01:02 PM
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):
Hope that helps
May 13 2020 07:03 PM - edited May 13 2020 07:10 PM
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.
May 14 2020 01:14 PM
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
May 17 2020 09:06 PM
Can you attach the worksheet you working on or a sample of it?
So I can reproduce the issue and figure it out.