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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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<at>tarkon.ee )


"bushtor" <[email protected]> wrote in message
news:[email protected]...
> 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
>