Excel 2007

wrighty50 · #1 05-12-2012, 09:37 AM

Hi all,

I run a report at work which I export into excel for further analysis. All of the data is presented fine in rows across. However one piece of data, i.e. the Account Number, which is a six digit number. This only appears once for each subset of data and I need to put this account number adjacent to each line item.

I realise this probably needs a macro but am not sure how to do this. I think I would need to add a loop, but I am a bit of a novice with vba and macros.

May be an offset forrmula could be written? Because the account number is consistently presented in the same location, however each set of company data varies in length.

I have attached an example (obviously the data is made up, but this is the exact way in which my report is presented). I have highlighted in yellow where each occurrence of the account number is and at the moment I have to manually copy that number to where I have highlighted in green. This is a shortened version of the data set and in reality there are several hundred Companys and thousands of line items

Any help would be greatly appreciated or even the code I could use

Thank you

macropod · #2 05-12-2012, 03:22 PM

Hi Wrighty,

Try:

Code:

Sub PropagateAcctIDs()
Application.ScreenUpdating = False
Dim LastRow As Long, i As Long, StrAcct As String
With ActiveSheet
  LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
  For i = 1 To LastRow
    If Cells(i, 9).Value = "Account Number" Then
      StrAcct = Cells(i, 10).Value
    ElseIf IsDate(Cells(i, 8).Value) Then
      Cells(i, 13).Value = StrAcct
    ElseIf Cells(i, 1).Value = "Client Name" Then
      With Cells(i, 13)
        .Value = "ACCOUNT NUMBER"
        .Font.Bold = True
      End With
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

wrighty50 · #3 05-13-2012, 01:08 PM

Hi Macropod,

thank you very much for your quick response. I give that code a try tomorrow and you know how it works.

Thanks again, much appreciated

wrighty50 · #4 05-13-2012, 02:24 PM

Hi again Macropod,

Just tried your code and it works perfectly!

That's fantastic, thank you very much for your help, much appreciated

Wrighty50

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro or routine required	evanhughes	Outlook	0	11-18-2011 02:56 AM
VBA code from Excel 2007 in Excel 2010	csam63	Excel Programming	1	10-07-2011 10:46 AM
Formula to select description when code is entered	Natasha	Excel	1	09-25-2011 12:59 PM
Formula Help Required	OTPM	Excel	6	08-31-2011 02:58 AM
Need Macro or Formula Help	mbocian	Excel	2	04-28-2011 02:04 AM