How do I make my macro insert/paste code in specific column range?

**Rabbitstew** · 07-17-2012, 02:51 PM

How do I make my macro insert/paste a specific code?

For example I want to paste a specific code for range of cells in a column:

Please Login or Register  to view this content.

The code I want to paste is:

Please Login or Register  to view this content.

How do I tie these together so when I run the macro it will automatically paste the code above into specific range in column?

Thanks in advance!

**efernandes67** · 07-17-2012, 04:19 PM

Try this:

1) Select the formula that you want to fill the range with and press CTRL + C (copy)
2) Start recording a new macro
3) Select the range F2 to F296
4) In the "Formula Bar" or press F2 and past the code
5) Hit CTRL + ENTER
6) Stop recording the macro

Look at the VBA code and I hope that this is what you want.

Elio Fernandes

**Rabbitstew** · 07-17-2012, 04:33 PM

okay, I got everything working!!

Here's the entire code:

Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1)), TrailingMinusNumbers:=True
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1)), TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Cells.Replace What:="N/A (US", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="See CRT", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="CHNU", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveCell.FormulaR1C1 = "UK Prog"
Cells.Replace What:="UK Prog", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="N/A US", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="N/A (Fo", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="N/A (UK", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="N/A (UK", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="C# Pend", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Columns("F:F").Select
Range("F19").Activate
Selection.Insert Shift:=xlToRight
Range("F2").Formula = "=IF(OR(ISNUMBER(SEARCH({""@"",""/""},E2))),"""",IF(ISERROR(RIGHT(RIGHT(E2,3),3-MIN(FIND(0,SUBSTITUTE(RIGHT(E2,3),{1,2,3,4,5,6,7,8,9},0)&0))+1)+0),"""",RIGHT(RIGHT(E2,3),3-MIN(FIND(0,SUBSTITUTE(RIGHT(E2,3),{1,2,3,4,5,6,7,8,9},0)&0))+1)+0))"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F300"), Type:=xlFillDefault
Range("F2:F300").Select
Columns("A:K").Select
Selection.Columns.AutoFit
Range("A1").Select
ActiveWindow.ScrollRow = 291
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 287
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 1
End Sub

Thanks again everyone for your help!