Macro help required...

[K@rl]

Hi Guys

Looking for someone to help put an Excel Macro together.

I have a list of names and addresses in Excel. I need to feed this information into our printing software and the Postcode has to be in a specific column - unfortunately the data I was sent is all over the place.

I'm thinking that a Macro might be the best way forward. I'll be creating a new column after all the address columns - the macro needs to look to the left of the current cell working its way across and copy the first cell containing a value and copying it to the post code column.



Hope that makes sense.

Not sure what the rules are but I'd be happy to compensate someone for their time.

 

[AHarvey]

EDIT:

First follow this to remove blank cells: http://www.exceltactics.com/automatically-delete-blank-cells-organize-data/

Then, enter this:

=INDEX(B4:F4, MATCH(FALSE, ISBLANK(B4:F4), 1))

set B4:F4 for the entire range of the address and make sure once you have typed it in, you don't just press enter, you press CTRL+SHIFT+ENTER

 

[andshrew]

A quick and simple VBA function to do this; in the Visual Basic editor add a new module a paste the code there.

To use, in your sheet type

=findField(cell)

eg. If your new post code field is K1, type that into K1 as

=findField(J1)

This moves to the left through the row (from the cell you supply to the function) until it finds a field with a value, or it runs out of columns. You can then just drag this down your new post code column as you usually would in Excel.

Option Explicit

Public Function findField(cell As Range) As String
    
    ' Auto re-calculate when sheet is changed
    Application.Volatile
    
    If cell.Count <> 1 Then Exit Function
    
    Dim i As Integer: i = 0
    Dim found As Boolean: found = False
    Dim foundText As String
    
    Do While Not found
        Dim offset As Range
        Set offset = cell.offset(0, i)
        
        If offset.Value <> "" Then
            foundText = offset.Value
            found = True
            Exit Do
        End If
        
        ' Check we're not about to run out of columns
        If offset.Column = 1 Then
            found = False
            Exit Do
        End If
        
        ' Move one cell to the left
        i = i - 1
    Loop
    
    Select Case found
        Case True
            ' field found
            findField = foundText
        
        Case False
            ' do something if nothing found?
            
    End Select
    
End Function

Hope that helps.

 

[K@rl]

Thanks guys. Will give it a try later on today, will let you know how I get on.

 

[K@rl]

A quick and simple VBA function to do this; in the Visual Basic editor add a new module a paste the code there.

Thanks for that - excuse my n00bness but I'm struggling to get it to work.

Getting this error when I paste the code in - probably just me being an idiot.



Is there a way that I can highlight the column required or set it as j:j? Sorry about the questions - my excel skills max out at making unuseful graphs

Cheers

 

[andshrew]

You need to type the =findField() into the actual cell that you want your post code in like you would a regular formula (so delete that bit from the top of your VBA module).

So going off your screenshot, into J1 type =findField(K1) . This will look from K1 through to A1 and return the first cell that had a value into J1. You can then click the bottom right hand corner of that cell and drag it down to copy the formula to the other rows automatically.

 

[K@rl]

Thanks again for the continued help - think I'm losing the will to live!

Got the formulas set in the cells - just trying to work out how to run the code.

 

[andshrew]

Sorry that's my mistake I misread your screenshot last night.

Change the K1 to I1 - it should be the cell directly to the left of the cell your typing findField into and K is on the right of J.