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

[Mag1c1an]

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").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

 

[Bob Phillips]

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").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"

 

[Allan Blount]

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!

 

[Bob Phillips]

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.

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

yeah, that would be a bit quicker <vbg>

Bob

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.