Solved: AutoFill Active Cell

[izet99]

Hi Everybody,

I need help on modifying vb scripts that I have posted. I'm working on excel file where I'm trying create vb code to auto fill a filtered column based on VLOOKUP data from another sheet. I have found this peace of code on this forum and I have modified a little bit to fit my needs. Here is issue that I have come across now.

There two issue:
1. When I run script it runs and FillDown thru my dynamic range but it fills all cells not just filtered.
2. Second issue is the Lookup Value is not showing up, instead of value I got: #N/A error, I must be doing something wrong with formula or something.

Any help on this would be greatly appreciated.

Here is peace of code that I have modified:

' Filter by Country to Assign Region
Selection.AutoFilter Field:=17, Criteria1:="US"

'Set Dynamic Range
Set DataRange = Range("P10", Range("P65536").End(xlUp))

'FillDown Column from S10 based on VLookUP from Region Sheet
Range("S10").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Region!R2C5:R51C6,2,FALSE)"

Set FillRange = ActiveCell
Set FillRange = FillRange(1, 1)

LastRow = Cells(65536, DataRange.Column).End(xlUp).Row
If LastRow > FillRange.Row Then
FillRange.AutoFill Range(FillRange.Address & ":" & _
Cells(LastRow, FillRange.Column).Address), xlFillDefault
End If

[p45cal]

try Filldown instead of autofill. Playing with it here shows it behaves as you want it.
Regarding the formula, there doesn't seem to be anything wrong with the syntax, but if it's referring to wrong ranges it won't give you the results you want. Can't tell without seeing your workbook.

[mdmackillop]

A sample workbook assists in identifying errors.

[izet99]

Thanks for your replay, I have try to us FillDown but I'm getting some error now. I have attached test file for your review. Most of the script is done with macro recorded along with peaces of code from here and there that I have found. When script is open it should ask you to open CSV file from the folder, it will copy file into MasterProcessing file, and then it will add few column, do some formatting and filtering. Main file is composed of Master, Region and Currency Sheets.

Master sheet is used only for setting up form button for quick run of the macro, i want to create like Dashboad for quick access since I'll have different file that I'll be processing. Region and Currency sheet is where my script will referring for VLOOKUP values.

In open/copied file I have column with country code so I want to VLOOKUP those country code from REGION sheet and assign Regions to those country.

Country code column contains all country along with US, so first I have to Filtered US and then assign Region for each US state. When that is done I have to assign region for rest of the county from another region table within Region sheet.

I hope I made it clear enough, I really appreciate your help on this issue. Let me if you have issue with opening attachment. Originaly I have zip it with Win RAR and just change file extention since it would not let me upload .rar file

Regards,
Izet

[mdmackillop]

Hi Izet
I can't open the zip file. You can post Excel files up to 1Mb using Manage Attachments in the Go Advanced reply section

[izet99]

Yeah, I figure that you might not be able to open, if you tried to change .zip extention back to .rar it might work. I'll send you file any way ...Actually I fix issue with formuala and I'm good with it, only issue is now that autofill all cells not only filtered.

Thanks a lot
Izet

[mdmackillop]

Something like

Sub FillFiltered()
Dim Rng As Range
Columns(3).AutoFilter Field:=1, Criteria1:="Data3"
Set Rng = Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
Rng.Offset(, 1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-2],Region!R2C1:R235C2,2,FALSE)"
Columns(3).AutoFilter
End Sub

[izet99]

hmm no, I have hard time incorporating this code with code I have.

Code I have posted almost work perfectly, only if I could figureout how to change or modify DataRange.AutoFill to FillDown

Thanks

[mdmackillop]

Unfortunately your code needs a text file to be imported. If you post some working code or sample, I can assist further. I feel though that the SpecialCells line gives the basic solution.

[izet99]

Thanks for your replay...here I'm uploading 1 file.

[izet99]

Here is second file....sorry maximum 1 file....

Thank you for help.

[mdmackillop]

Try this

'Filter Country to Assign Region = US Only
Selection.AutoFilter Field:=17, Criteria1:="US"
'Set DataRange
Set DataRange = Range("P10", Range("P65536").End(xlUp))
'Select Cell and Add Formula to it
LastRow = Cells(65536, DataRange.Column).End(xlUp).Row
If LastRow > 10 Then
DataRange.Offset(, 3).SpecialCells(xlCellTypeVisible).Formula = "=VLOOKUP(RC[-3],Region!R2C5:R51C6,2,FALSE)"
End If
'Set AutoFilter = Show All Data
Selection.AutoFilter Field:=17
'Filter Country to Assign Region = All Country except US
Selection.AutoFilter Field:=17, Criteria1:="<>US"
'Set DataRange
Set DataRange = Range("P10", Range("P65536").End(xlUp))
LastRow = Cells(65536, DataRange.Column).End(xlUp).Row
If LastRow > 10 Then
DataRange.Offset(, 3).SpecialCells(xlCellTypeVisible).Formula = "=VLOOKUP(RC[-2],Region!R2C1:R235C2,2,FALSE)"
End If
Columns("R:S").AutoFit

[mdmackillop]

A recorded macro involves many unneccessary steps. Remove Selection and refer to ranges directly, or by way of an assigned variable.

[izet99]

Wow, that worked perfectly, nice..thank you, thank you.

Yes, I figure that have extra staff in code since I have used macro recorded. I'm new to VBA so I'm not familiar will all these shortcuts Thank you on cleaning up my code, its much faster now... I did fallow your style to update my other macros to improve speed. Thanks a lot for you help and support on this issue. If there were no anything else from you I would consider this issue as closed.
:beerchug:

Izet