I get a Type Mismatch error with this code.
Where I want to start at cell A8 to the last entry in col A and produce the results to col H, H8 and down.

Then code to copy paste special values H8 to end of column in place.

Sub ConcTestx() 'Type Mismatch error
Dim LastRow As Long
Dim Ws As Worksheet

Set Ws = Sheets("Sheet1")
LastRow = Ws.Range("A8:A" & Range("A" & Rows.Count).End(xlUp).Row)

Ws.Range("H8:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2"
'paste special values in place here
End Sub

Thanks,
Howard

hi Howard,

Sub ConcTestx()
Dim LastRow As Long
Dim Ws As Worksheet

Set Ws = Sheets("Sheet1")
LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row

Ws.Range("H8:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2"
Ws.Range("H8:H" & LastRow) = Ws.Range("H8:H" & LastRow).Value
End Sub

isabelle

Le 2013-02-10 21:55, Howard a écrit :
I get a Type Mismatch error with this code.
Where I want to start at cell A8 to the last entry in col A and produce the results to col H, H8 and down.

Then code to copy paste special values H8 to end of column in place.

Sub ConcTestx() 'Type Mismatch error
Dim LastRow As Long
Dim Ws As Worksheet

Set Ws = Sheets("Sheet1")
LastRow = Ws.Range("A8:A" & Range("A" & Rows.Count).End(xlUp).Row)

Ws.Range("H8:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2"
'paste special values in place here
End Sub

Thanks,
Howard

On Sunday, February 10, 2013 6:55:32 PM UTC-8, Howard wrote:
I get a Type Mismatch error with this code.

Where I want to start at cell A8 to the last entry in col A and produce the results to col H, H8 and down.



Then code to copy paste special values H8 to end of column in place.



Sub ConcTestx() 'Type Mismatch error

Dim LastRow As Long

Dim Ws As Worksheet



Set Ws = Sheets("Sheet1")

LastRow = Ws.Range("A8:A" & Range("A" & Rows.Count).End(xlUp).Row)



Ws.Range("H8:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2"

'paste special values in place here

End Sub



Thanks,

Howard

Thank you, Isabelle,

Works perfect!
I seem to always struggle to get away from Select Copy Paste Special Values.

Much appreciated

Regards,
Howard