Selection.FillDown Help

[drozek]

I will start this off, I don't program at all, I have been recording and modifying code macros to help automate some things at work. I have this code that's not working when I run it:

Columns("C:C").Select
ActiveSheet.Range("$C$1:$C$817").AutoFilter Field:=3, Criteria1:="202*"
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "'202 DIA"
With Worksheets("Data").AutoFilter.Range
Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
Selection.FillDown

I want it to fill down all the filtered cells that start with 202 with 202 DIA.

Any help?

 

[Fluff]

A few questions
1) are the values in Col C text or numbers?
2) Is col C hard values or formulae?
3) do you want to put 202 DIA in col D?

 

[drozek]

1) Text
2) Hard Values
3) No just in C

 

[Fluff]

In that case, how about

Sub drozek()
   With Range("C1", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(left(@,3)=""202"",""202 DIA"",@)", "@", .Address))
   End With
End Sub

 

[drozek]

In that case, how about

Sub drozek()
   With Range("C1", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(left(@,3)=""202"",""202 DIA"",@)", "@", .Address))
   End With
End Sub

Worked perfectly! Thanks for your help!

 

[Fluff]

You're welcome & thanks for the feedback

 

[drozek]

You're welcome & thanks for the feedback

So I have another one very similar:

I am trying to filter in Col E, the first 4 numbers, 7101, and replace the cell in F with "Cott", is it the same formulaish?

With Range("E1", Range("E" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("if(left(@,3)=""7101"",""Cott"",@)", "@", .Address))
End With
End Sub

 

[Fluff]

If you want to replace the values in Col E with "Cott" you need to make this change

left(@,[COLOR=#ff0000]4[/COLOR])

to look at the first 4 digits.
To put Cott in col F use

Sub drozek()
   With Range("F1", Range("E" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("if(left(" & .Offset(, -1).Address & ",4)=""7101"",""Cott"",@)", "@", .Address))
   End With
End Sub

 

[drozek]

If you want to replace the values in Col E with "Cott" you need to make this change

left(@,[COLOR=#ff0000]4[/COLOR])

to look at the first 4 digits.
To put Cott in col F use

Sub drozek()
   With Range("F1", Range("E" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("if(left(" & .Offset(, -1).Address & ",4)=""7101"",""Cott"",@)", "@", .Address))
   End With
End Sub

God this is golden! ok I might have a few more questions, what if I wanted to look for 347 in the in col e, but start with the second digit?

 

[Fluff]

You'd use Mid(...,2,3) instead of left(...,4)