Simple loop?
 

Hi,

Say column B in an Excel sheet contains ForeNames and column C contains
FamilyNames, how would I merge these two together in an inserted Column
D as <forename<space<familyname?

Also another column contains numeric values with 3 or 4 digits. I need
to pad these numbers with a leading zeros making 234 to 0234 and also
convert this field to a string field to make sure that excel doesn't
strip this zero off again. How would I do this..?

Thanks a lot if someone has a couple of code snippets which can give me
a point of departure with routines like this in the future

regards

tor

Simple loop?
 

The first bit is easy enough without code. In D1 type =B1&" "&C1

To change the numbers to a string, select the column, go Format|Cells|Number
and select Category as Text.

To pad the numbers use this code

For r = 1 To 10 ' change numbers to suit row range
If Len(Cells(r, 3)) < 4 Then ' check length of entry in current row column 3
(C)
Cells(r, 3).Value = String(4 - Len(Cells(r, 3)), "0") & Cells(r, 3).Value '
pads out string with leading zeros
End If
Next

The If line is only there in case you have a cell with more than 4 digits.
--
Ian
--
"bushtor" wrote in message
oups.com...
Hi,

Say column B in an Excel sheet contains ForeNames and column C contains
FamilyNames, how would I merge these two together in an inserted Column
D as <forename<space<familyname?

Also another column contains numeric values with 3 or 4 digits. I need
to pad these numbers with a leading zeros making 234 to 0234 and also
convert this field to a string field to make sure that excel doesn't
strip this zero off again. How would I do this..?

Thanks a lot if someone has a couple of code snippets which can give me
a point of departure with routines like this in the future

regards

tor

Simple loop?
 

Hi bushtor,

Say column B in an Excel sheet contains ForeNames and column C contains
FamilyNames, how would I merge these two together in an inserted Column
D as <forename<space<familyname?

Try this:

Sub test()
With ActiveSheet
Set rng = .Range(.Cells(2, "B"), _
.Cells(.Rows.Count, "B").End(xlUp))
End With
For Each c In rng
c.Offset(, 2) = Trim(c & " " & c.Offset(, 1))
Next c
End Sub


Also another column contains numeric values with 3 or 4 digits. I need
to pad these numbers with a leading zeros making 234 to 0234 and also
convert this field to a string field to make sure that excel doesn't
strip this zero off again. How would I do this..?

Sub test2()
With ActiveSheet
Set rng = .Range(.Cells(2, "B"), _
.Cells(.Rows.Count, "B").End(xlUp))
End With
For Each c In rng
c.NumberFormat = "@"
c = Format(c, "0000")
Next c
End Sub

Regards,
KL

Simple loop?
 

Hi

To convert numbers to text strings with leading 0's, you can use an
worksheet formula like
=TEXT(A1,"0000")

An equivalent in VBA is
TextValue = Format(NumericValue,"0000")


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"bushtor" wrote in message
oups.com...
Hi,

Say column B in an Excel sheet contains ForeNames and column C contains
FamilyNames, how would I merge these two together in an inserted Column
D as <forename<space<familyname?

Also another column contains numeric values with 3 or 4 digits. I need
to pad these numbers with a leading zeros making 234 to 0234 and also
convert this field to a string field to make sure that excel doesn't
strip this zero off again. How would I do this..?

Thanks a lot if someone has a couple of code snippets which can give me
a point of departure with routines like this in the future

regards

tor