To make code more efficient

[Fin Fang Foom]

Hi everyone,

I recorded a macro. What I did I click inside the cells and hit enter. I stop the macro and it gave me this code. Does anyone knows a faster way of doing this ? I need a range say B2:B3000.

  Range("B2").Select
    ActiveCell.FormulaR1C1 = "ProdType1"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "ProdType2"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "ProdType2"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "ProdType2"
    Range("B6").Select

 

[jindon]

With Range("B2")
    .Value = "ProdType1"
    .AutoFill .Resize(5)
End With

Oops,
should be

With Range("B2")
    .Value = "ProdType1"
    .Offset(1).Resize(3000 - 2) = "ProdType2"
End With

 

[erik.van.geit]

Hi, Fin Fang Foom !

or did you mean this

With Range("B2:B300")
.FormulaR1C1 = "=""ProdType"" & row() - 1"
.Value = .Value
End With

kind regards,
Erik

Hi, jindon

 

[Fin Fang Foom]

Thanks for replying. It woks fine.

 

[jindon]

ADVERTISEMENT

Erik,

I thought it would be like that, but his code....

With Range("B2")
.Value = "ProdType1"
.AutoFill .Resize(3000-1)
End With

 

[Fin Fang Foom]

I like both of your codes and it works fine. But what I was trying to do it instead of me clicking inside of each cell and hit enter for each of them it will activate the worksheet event code in my worksheet.

So I found this code and it works fine now. But both of you provided the codes that I will use.

Thanks!

Sub Activate_Cell()
Range("B2").Select
ActiveCell.Value = Range("B2:B3000").Value
End Sub

 

[erik.van.geit]

Sub Activate_Cell() 
Range("B2").Select 
ActiveCell.Value = Range("B2:B3000").Value 
End Sub

what is this supposed to do ?
you cannot put 3000 values in one cell
for me this code doesn't do anything: or do I miss something ?

best regards,
Erik