Trying To Enter A Formula Into Every Cell In A Column.

Hiya, I have been playing around with the macro record button, but im coming
a little unstuck in a couple of places.

I am trying to assign either of the two formula into every cell in column H
too join the text values together in a single value.

=E1&""&F1&G1
or
=CONCATENATE(E1,F1,G1)

although i know that either of these formula will do the job, i am not
seeing the value displayed, the formula is staying displayed.

Also, i would like to know how to insert two, columns at once, say after E,
and how to join the Text to Columns function that i currently have being
done in two processes so that it can be done in one process.

Here is the code i have so far.

Columns("A:B").Select
Selection.ColumnWidth = 5
Columns("C:C").Select
Selection.ColumnWidth = 12
Columns("D:D").Select
Selection.ColumnWidth = 32
Columns("E:F").Select
Selection.ColumnWidth = 10
Columns("G:G").Select
Selection.ColumnWidth = 8
Columns("A:G").Select
Range("G1").Activate
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("F:F").Select
Selection.Insert Shift:=x1ToRight
Selection.Insert Shift:=x1ToRight
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(2, 2)),
TrailingMinusNumbers:=True
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(1, 2)),
TrailingMinusNumbers:=True
Range("E:E,G:G").Select
Selection.ColumnWidth = 4
Columns("F:F").Select
Selection.ColumnWidth = 2
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 10
Range("H:H").Select
ActiveCell.FormulaR1C1 = "=E1&""""&F1&G1"
End Sub

"Mag1c1an" <[email protected]> wrote in message
news:[email protected]...
> Hiya, I have been playing around with the macro record button, but im
coming
> a little unstuck in a couple of places.
>
> I am trying to assign either of the two formula into every cell in column
H
> too join the text values together in a single value.
>
> =E1&""&F1&G1
> or
> =CONCATENATE(E1,F1,G1)
>
> although i know that either of these formula will do the job, i am not
> seeing the value displayed, the formula is staying displayed.

Is the column formatted as text?


> Also, i would like to know how to insert two, columns at once, say after
E,
> and how to join the Text to Columns function that i currently have being
> done in two processes so that it can be done in one process.

Columns("F:G").Insert Shift:=x1ToRight


> Here is the code i have so far.
>
> Columns("A:B").Select
> Selection.ColumnWidth = 5
> Columns("C:C").Select
> Selection.ColumnWidth = 12
> Columns("D:D").Select
> Selection.ColumnWidth = 32
> Columns("E:F").Select
> Selection.ColumnWidth = 10
> Columns("G:G").Select
> Selection.ColumnWidth = 8
> Columns("A:G").Select
> Range("G1").Activate
> Selection.Sort Key1:=Range("E1"), Order1:=xlAscending,
Header:=xlGuess,
> _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Columns("F:F").Select
> Selection.Insert Shift:=x1ToRight
> Selection.Insert Shift:=x1ToRight
> Columns("E:E").Select
> Selection.TextToColumns Destination:=Range("E1"),
> DataType:=xlFixedWidth, _
> FieldInfo:=Array(Array(0, 2), Array(2, 2)),
> TrailingMinusNumbers:=True
> Columns("F:F").Select
> Selection.TextToColumns Destination:=Range("F1"),
> DataType:=xlFixedWidth, _
> FieldInfo:=Array(Array(0, 2), Array(1, 2)),
> TrailingMinusNumbers:=True
> Range("E:E,G:G").Select
> Selection.ColumnWidth = 4
> Columns("F:F").Select
> Selection.ColumnWidth = 2
> Columns("H:H").Select
> Selection.Insert Shift:=xlToRight
> Selection.ColumnWidth = 10
> Range("H:H").Select
> ActiveCell.FormulaR1C1 = "=E1&""""&F1&G1"
> End Sub

You rarely need to select. For instance,

Columns("A:B").Select
Selection.ColumnWidth = 5

is better written as

Columns("A:B").ColumnWidth = 5

and

Range("H:H").Select
ActiveCell.FormulaR1C1 = "=E1&""""&F1&G1"

becomes

Range("H:H").SelectFormulaR1C1 = "=E1&F1&G1"

Now you come to mention it i think the column maybe formatted as text,
but i did try and change it and nothing happened.

I wasnt to sure what to format the column as to show the values of the
formuala as i have never come across this before?

BTW, thanks for your swift reply.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Allan,

Unfortunately, if it was text, changing the column back to general does
solve it. You need to edit each cell with the formula to revert it.

--
HTH

-------

Bob Phillips
"Allan Blount" <[email protected]> wrote in message
news:%[email protected]...
> Now you come to mention it i think the column maybe formatted as text,
> but i did try and change it and nothing happened.
>
> I wasnt to sure what to format the column as to show the values of the
> formuala as i have never come across this before?
>
> BTW, thanks for your swift reply.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

or rather than edit each cell, after formatting as general (or other than
Text), select the cells and do Edit=>Replace

Replace What =
Replace With =

so in each box put in an equal sign. Then click replace all and the
formulas should display the values.

--
Regards,
Tom Ogilvy

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Allan,
>
> Unfortunately, if it was text, changing the column back to general does
> solve it. You need to edit each cell with the formula to revert it.
>
> --
> HTH
>
> -------
>
> Bob Phillips
> "Allan Blount" <[email protected]> wrote in message
> news:%[email protected]...
> > Now you come to mention it i think the column maybe formatted as text,
> > but i did try and change it and nothing happened.
> >
> > I wasnt to sure what to format the column as to show the values of the
> > formuala as i have never come across this before?
> >
> > BTW, thanks for your swift reply.
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
>

yeah, that would be a bit quicker <vbg>

Bob


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> or rather than edit each cell, after formatting as general (or other than
> Text), select the cells and do Edit=>Replace
>
> Replace What =
> Replace With =
>
> so in each box put in an equal sign. Then click replace all and the
> formulas should display the values.
>
> --
> Regards,
> Tom Ogilvy
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Allan,
> >
> > Unfortunately, if it was text, changing the column back to general does
> > solve it. You need to edit each cell with the formula to revert it.
> >
> > --
> > HTH
> >
> > -------
> >
> > Bob Phillips
> > "Allan Blount" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Now you come to mention it i think the column maybe formatted as text,
> > > but i did try and change it and nothing happened.
> > >
> > > I wasnt to sure what to format the column as to show the values of the
> > > formuala as i have never come across this before?
> > >
> > > BTW, thanks for your swift reply.
> > >
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> > > Don't just participate in USENET...get rewarded for it!
> >
> >
>
>