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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,453
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?
 
Upvote 0

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>
 
Upvote 0

Fluff

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

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
 
Upvote 0

Fluff

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

Forum statistics

Threads
1,186,217
Messages
5,956,610
Members
438,257
Latest member
Mark_365

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top