#1
|
|||
|
|||
Excel 2007 - formula or macro/vba code required
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 |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |