Need to create Excel Macro?

[Mascot]

Hi,

I have a spreadsheet were I have list of accounts and they are catergorized
by location in the same column. for example in Column B

1001 (Account)
1002 (Account)
1003 (Account)
1004 (Account)
1005 (Account)
J101(Location)
1001 (Account)
1002 (Account)
1003 (Account)
1005 (Account)
J102 (Location)

so it will list the accounts and then the last item will be the location.
What I want to do is have the location in column a right next to the account.
Doea anyone have a macro that can go down the list put the location in front
of the account?

Thanks
Mascot

[VBA Noob]

Assuming all your data has a space why don't you just use a formula like the one below ??


=RIGHT(E1,LEN(E1)-FIND(" ",E1,1))&" "&LEFT(E1,FIND(" ",E1,1)-1)

[JMB]

Assuming your locations all begin w/a letter (so it is not possible they
could be interpreted as numbers). After putting the location in the column
to the left of your data, this macro also deletes the rows in your data that
had the locations, so be sure to backup your data before trying. Also, you
have to select the data you want the macro to run on beforehand.

Sub test()
Dim rngCell As Range
Dim rngDelete As Range
Dim rngTemp As Range

If Selection.Columns.Count > 1 Then End

For Each rngCell In Selection.Cells
If Not IsNumeric(rngCell.Value) Then
If rngTemp Is Nothing Then
rngCell.Copy Range(Selection.Cells(1)(1, 0), rngCell(0, 0))
Else: rngCell.Copy Range(rngTemp(2, 0), rngCell(0, 0))
End If
Set rngTemp = rngCell
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngCell, rngDelete)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireRow.Delete

End Sub

"Mascot" wrote:

> Hi,
>
> I have a spreadsheet were I have list of accounts and they are catergorized
> by location in the same column. for example in Column B
>
> 1001 (Account)
> 1002 (Account)
> 1003 (Account)
> 1004 (Account)
> 1005 (Account)
> J101(Location)
> 1001 (Account)
> 1002 (Account)
> 1003 (Account)
> 1005 (Account)
> J102 (Location)
>
> so it will list the accounts and then the last item will be the location.
> What I want to do is have the location in column a right next to the account.
> Doea anyone have a macro that can go down the list put the location in front
> of the account?
>
> Thanks
> Mascot
>

[JMB]

Another way you could do it is to use formulae

Enter in cell A1:

=IF(ISTEXT(B1),"",IF(ISTEXT(B2),B2,A2))
and copy down. If you want the data hardcoded, select column A and copy,
then Edit/Paste Special-values. After this, if you want to get rid of the
locations in your data in column B, use Autofilter to filter for blanks in
column A, delete the filtered rows, then turn off Autofilter.



"JMB" wrote:

> Assuming your locations all begin w/a letter (so it is not possible they
> could be interpreted as numbers). After putting the location in the column
> to the left of your data, this macro also deletes the rows in your data that
> had the locations, so be sure to backup your data before trying. Also, you
> have to select the data you want the macro to run on beforehand.
>
> Sub test()
> Dim rngCell As Range
> Dim rngDelete As Range
> Dim rngTemp As Range
>
> If Selection.Columns.Count > 1 Then End
>
> For Each rngCell In Selection.Cells
> If Not IsNumeric(rngCell.Value) Then
> If rngTemp Is Nothing Then
> rngCell.Copy Range(Selection.Cells(1)(1, 0), rngCell(0, 0))
> Else: rngCell.Copy Range(rngTemp(2, 0), rngCell(0, 0))
> End If
> Set rngTemp = rngCell
> If rngDelete Is Nothing Then
> Set rngDelete = rngCell
> Else: Set rngDelete = Union(rngCell, rngDelete)
> End If
> End If
> Next rngCell
>
> If Not rngDelete Is Nothing Then _
> rngDelete.EntireRow.Delete
>
> End Sub
>
> "Mascot" wrote:
>
> > Hi,
> >
> > I have a spreadsheet were I have list of accounts and they are catergorized
> > by location in the same column. for example in Column B
> >
> > 1001 (Account)
> > 1002 (Account)
> > 1003 (Account)
> > 1004 (Account)
> > 1005 (Account)
> > J101(Location)
> > 1001 (Account)
> > 1002 (Account)
> > 1003 (Account)
> > 1005 (Account)
> > J102 (Location)
> >
> > so it will list the accounts and then the last item will be the location.
> > What I want to do is have the location in column a right next to the account.
> > Doea anyone have a macro that can go down the list put the location in front
> > of the account?
> >
> > Thanks
> > Mascot
> >