Excel VBA problem
 

Hi
I wonder if you can help me. I am attaching an Excel 2000 file tha
shows and extract from our Financial System. Basically there are
columns- Cost Centre, Accounting Codes and £.
There is a cost centre name (in red) in the subtotal row in th
Accounting Codes column. What I would like to do is to write a macr
that will search the column "Accounting Codes" for the cost centre nam
and then paste that name in the Cost Center column for each of the row
that belong to that cost centre up to the subtotal line.

Thank you in advance
Regards
Nija

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45028
--
Message posted from http://www.ExcelForum.com

Excel VBA problem
 

Hi Nijan
put the following code in a module of your workbook:

-----
Sub insert_cc()
Dim rng As Range
Dim row_count
Dim column_no
Dim cost_center

Set rng = Selection
With rng
If .Columns.Count 1 Then Exit Sub
column_no = .Column
For row_count = (.rows.Count + .Row - 1) To .Row Step -1
If Cells(row_count, column_no).Value < "" Then
cost_center = Cells(row_count, column_no + 1)
ElseIf Cells(row_count, column_no + 1).Value < "" Then
Cells(row_count, column_no).Value = cost_center
End If
Next
End With
End Sub

----

Now do the following:
- select your column which has to be filled (e.g. your cells in column
A)
- start this macro

It requieres that all subtotals have an entry in column A (so in your
example the last row has to contain an entry for the last cost center)



--
Regards
Frank Kabel
Frankfurt, Germany

Hi
I wonder if you can help me. I am attaching an Excel 2000 file that
shows and extract from our Financial System. Basically there are 3
columns- Cost Centre, Accounting Codes and £.
There is a cost centre name (in red) in the subtotal row in the
Accounting Codes column. What I would like to do is to write a macro
that will search the column "Accounting Codes" for the cost centre
name and then paste that name in the Cost Center column for each of
the rows that belong to that cost centre up to the subtotal line.

Thank you in advance
Regards
Nijan

Attachment filename: test.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=450283 ---
Message posted from http://www.ExcelForum.com/

Excel VBA problem
 

Hello Frank


I tried your VBA code and applied it as you suggested.

It worked perfectly and gave me exactly the solution that I needed.

Thank you very much. Your help has been much appreciated.

Kind Regards
Nija

--
Message posted from http://www.ExcelForum.com

Excel VBA problem
 

Hi Nijan
thanks for the feedback. Glad to be of some help

--
Regards
Frank Kabel
Frankfurt, Germany

Hello Frank


I tried your VBA code and applied it as you suggested.

It worked perfectly and gave me exactly the solution that I needed.

Thank you very much. Your help has been much appreciated.

Kind Regards
Nijan


---
Message posted from http://www.ExcelForum.com/

Excel VBA problem
 

Just paste this formula into all your blank cells of Column A that are next to data in B column.

=INDEX(B4:$B$1000,MATCH("*bank*",B4:$B$1000,0))

Increase the 1000 if your rows extend further.
First select your range by selecting A3 then Control + *
Use F5, Special Blanks to select your cells.
Paste the formula to the formula bar, then press Control + Enter.
No VBA code is necessary, although if you must have it, just record the above to see it.


Regards Robert


"nijan_shah " wrote in message ...
Hi
I wonder if you can help me. I am attaching an Excel 2000 file that
shows and extract from our Financial System. Basically there are 3
columns- Cost Centre, Accounting Codes and £.
There is a cost centre name (in red) in the subtotal row in the
Accounting Codes column. What I would like to do is to write a macro
that will search the column "Accounting Codes" for the cost centre name
and then paste that name in the Cost Center column for each of the rows
that belong to that cost centre up to the subtotal line.

Thank you in advance
Regards
Nijan

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=450283
---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 21/02/2004