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

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

"Mag1c1an" wrote in message
...
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"

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

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!

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

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" wrote in message
...
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!

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

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" wrote in message
...
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" wrote in message
...
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!

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

yeah, that would be a bit quicker <vbg

Bob


"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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!