Auto Fill macro

[Alvaroro84]

Selection.AutoFill Destination:=Sheets("Sheet1").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible), Type:=xlFillDefault

I try to use this to code to auto fill all visible cells in column E. The reason it has to be visible rows is because it runs a filter before the code. However, the code above seem to be wrong can some one help me fix the above code in a similar format if possible

 

[Max1616]

I don't think you can do what you're trying to do with auto fill. A loop should do the trick though:

For i = 3 To lastrow
    If Range("E" & i).EntireRow.Hidden = False Then
        Range("E2").Copy Range("E" & i)
    End If
Next i

 

[Alex Blakenburg]

If you are not trying to copy formatting then this might work for you.

Sheets("Sheet1").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Sheets("Sheet1").Range("E2").FormulaR1C1

 

[Alvaroro84]

If you are not trying to copy formatting then this might work for you.

Sheets("Sheet1").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Sheets("Sheet1").Range("E2").FormulaR1C1

ActiveSheet.Range("A1").AutoFilter field:=4, Criteria1:="0"
    ActiveSheet.Range("D2:D" & Rows.count).SpecialCells(xlCellTypeVisible).Range("A1").Select
ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],'[ccc]Cos'!R2C3:R400000C4,2,False)"
      
Sheets("Sheet1").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Sheets("Sheet1").Range("D2").FormulaR1C1

The code above is what I'm working with and it does not seem to be doing the job when it fills the data i fills it with numbers and its suppose to fill it with word

 

[Alvaroro84]

I don't think you can do what you're trying to do with auto fill. A loop should do the trick though:

For i = 3 To lastrow
    If Range("E" & i).EntireRow.Hidden = False Then
        Range("E2").Copy Range("E" & i)
    End If
Next i

ActiveSheet.Range("A1").AutoFilter field:=4, Criteria1:="0"
    ActiveSheet.Range("D2:D" & Rows.count).SpecialCells(xlCellTypeVisible).Range("A1").Select
ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],'[ccc]Cos'!R2C3:R400000C4,2,False)"

For i = 3 To lastrow
    If Range("E" & i).EntireRow.Hidden = False Then
        Range("E2").Copy Range("E" & i)
    End If
Next i

The code above is what I'm working with and it does not seem to be doing the job when it fills the data i fills it with numbers and its suppose to fill it with word

 

[Max1616]

Is the loop copying the formula down correctly? If so, then perhaps the issue is with your vlookup formula?

 

[Alvaroro84]

No the loop isn't copying down correctly

 

[Alvaroro84]

Is the loop copying the formula down correctly? If so, then perhaps the issue is with your vlookup formula?

No the Loop doenst seem to be copying down correctly I've tested the code without the loop and the formula seems to work

 

[Max1616]

Is the variable "Lastrow" defined in your macro? Is the sheet this is running on, the active sheet in the workbook? These are the only things I can think of stopping the macro from running.

lastrow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 3 To lastrow
    If Range("E" & i).EntireRow.Hidden = False Then
        Range("E2").Copy Range("E" & i)
    End If
Next i

 

[Alvaroro84]

Is the variable "Lastrow" defined in your macro? Is the sheet this is running on, the active sheet in the workbook? These are the only things I can think of stopping the macro from running.

lastrow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 3 To lastrow
    If Range("E" & i).EntireRow.Hidden = False Then
        Range("E2").Copy Range("E" & i)
    End If
Next i

I have it as this

Dim LastRow As Long