Cant combine cells to single string and write to cell

PanzerRanger

New Member
Joined
Jan 3, 2018
Messages
20
Using Excel 2013

If Im runing the code below in a for loop then comb works fine.

For Each c In Range("B2:B10") 'ersätt range
nr = Right(c, 3)
If nr = 980 Then
unr = c.Offset(0, -1)
se = c.Offset(0, 0)
comb = unr & " Sektion" & " " & se
Range("c4") = comb
End If
Next



but if comb looks like this
comb = unr & " Sektion " & se or comb = unr &" " & "Sektion" & " " & se

it generates a Type error 1004, but I need the spaces, any help because Im stuck out here

Many thanks
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,612
Office Version
  1. 365
Platform
  1. Windows
This works for me
Code:
For Each c In Range("B2:B10") 'ersätt range
nr = Right(c, 3)
If nr = 980 Then
unr = c.Offset(0, -1)
se = c.Offset(0, 0)
comb = unr & " Sektion " & se
Range("c4") = comb
End If
Next
What are the values of unr & se when it fails?
 

PanzerRanger

New Member
Joined
Jan 3, 2018
Messages
20
Sheet looks like this

Col A Col B
=H9K1191000
=B4T1191980
=B4B1201981
=B4B1211982
=B4B1221983
=B4B1231990

<colgroup><col><col></colgroup><tbody>
</tbody>
 

PanzerRanger

New Member
Joined
Jan 3, 2018
Messages
20

ADVERTISEMENT

Apparently I cant use 2 spaces
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,612
Office Version
  1. 365
Platform
  1. Windows
If you actually have an = sign in the col A values try
Code:
comb = "'" & unr & " Sektion " & se
 

PanzerRanger

New Member
Joined
Jan 3, 2018
Messages
20
I noticed ;) Thanks! It also worked if I change the cell data type. I guess its easy to miss those little things

Cheers
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,612
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,472
Messages
5,529,035
Members
409,849
Latest member
J7House1984
Top