CONCATENATE code

[Howard]

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

 

[isabelle]

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 :

 

[Howard]

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