learning VBA....simple task, requesting help please

[Michael C]

[Excel 2003]
Hi,

I have been reading VBA for dummies and have been having trouble
trying to accomplish some simple vba tasks.

I have a report that is generated from third party software. The
number of rows and columns of data can vary but at the very least the
report will always start on Row 2 and data will fill at least the
first three columns.

for example:

A B
C D

1

2 Customer Name Route Flag

3 Smith Joe 2 X

4 Collins Tom 1 X

5 Jones Peter 2 X


I am trying to write a macro that would concatenate the two name
fields into a single cell. Because the amount of data can vary I can
not use the concatenate function as the macro has to be intelligent
enough to detect the first empty column.

I have written some code, at least starting with a static column but
have had no luck. Here is the code I have wrote (it has gone through
several iterations so my loop type choices, etc may seem odd).

Sub concatenate()

Dim FirstName As Range
Dim LastName As Range
Dim DestCell As Range

Set FirstName = Worksheets("Sample Report").Range("a3")
Set LastName = Worksheets("Sample Report").Range("b3")
Set DestCell = Worksheets("Sample Report").Range("j3")

Do Until IsEmpty(FirstName.Value)

DestCell.Value = FirstName & ", " & LastName
FirstName.Offset(1, 0).Activate
LastName.Offset(1, 0).Activate
DestCell.Offset(1, 0).Activate

Loop

End Sub

I have tried several things. I either get it to concatenate the first
row and copy that into every single destination cell in the
spreadsheet, get the code stuck toggling between two active cells (I
believe that is how it is now), or any number of compile erriors, etc
if I play with the offset properties.

Any help is greaty appreciated.

Thanks.

-Mike

 

[Arvi Laanemets]

Hi

Why to make your task deliberately harder, and your table structure more
messy?

Currently you table(s) has/have structure
Customer Info (2 colums) - Data (some columns) - Flag (1 column)

Logically the new structure must remain same - only you'll have 3 columns in
Customer Info group (FirstName, LastName, FullName).

So the solution will be: Insert an empty column after first 2, and use
concantenation to insert full names into this column. What is to right of
this column, is irrelevant for current task.

 

[Mike H]

Michael,

How about this slightly different approach. It takes the used range of
Column A and concatenates that and the adjacent value in Column B to Column J

Sub sonic()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
c.Offset(, 9).Value = c.Value & " " & c.Offset(, 1).Value
Next
End Sub

Mike

 

[Michael C]

Hi

Why to make your task deliberately harder, and your table structure more
messy?

Currently you table(s) has/have structure
Customer Info (2 colums) - Data (some columns) - Flag (1 column)

Logically the new structure must remain same - only you'll have 3 columns in
Customer Info group (FirstName, LastName, FullName).

So the solution will be: Insert an empty column after first 2, and use
concantenation to insert full names into this column. What is to right of
this column, is irrelevant for current task.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

[Excel 2003]
Hi,

I have been reading VBA for dummies and have been having trouble
trying to accomplish some simple vba tasks.

I have a report that is generated from third party software. The
number of rows and columns of data can vary but at the very least the
report will always start on Row 2 and data will fill at least the
first three columns.

for example:

A B
C D

2 Customer Name Route Flag

3 Smith Joe 2 X

4 Collins Tom 1 X

5 Jones Peter 2 X

I am trying to write a macro that would concatenate the two name
fields into a single cell. Because the amount of data can vary I can
not use the concatenate function as the macro has to be intelligent
enough to detect the first empty column.

I have written some code, at least starting with a static column but
have had no luck. Here is the code I have wrote (it has gone through
several iterations so my loop type choices, etc may seem odd).

Sub concatenate()

Dim FirstName As Range
Dim LastName As Range
Dim DestCell As Range

Set FirstName = Worksheets("Sample Report").Range("a3")
Set LastName = Worksheets("Sample Report").Range("b3")
Set DestCell = Worksheets("Sample Report").Range("j3")

Do Until IsEmpty(FirstName.Value)

DestCell.Value = FirstName & ", " & LastName
FirstName.Offset(1, 0).Activate
LastName.Offset(1, 0).Activate
DestCell.Offset(1, 0).Activate

End Sub

I have tried several things. I either get it to concatenate the first
row and copy that into every single destination cell in the
spreadsheet, get the code stuck toggling between two active cells (I
believe that is how it is now), or any number of compile erriors, etc
if I play with the offset properties.

Any help is greaty appreciated.

-Mike

Thanks for all the ideas. I will try them out.

-Mike