CONCATENATE code


H

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
 
Ad

Advertisements

I

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 :
 
Ad

Advertisements

H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top