Excel VBA problem

  • Thread starter Thread starter nijan_shah
  • Start date Start date
N

nijan_shah

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
 
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)
 
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
 
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
 
Back
Top