Reply to: I have a stupid question

[Pondlife]

Oops I only meant that BoredBloke is a better VBA programmer than I am. Mainly because I'm not a VBA programmer but can blag a few things here and there

You've not read Suity's new rules, have you.

[mudskipper]

Oops I only meant that BoredBloke is a better VBA programmer than I am. Mainly because I'm not a VBA programmer but can blag a few things here and there

The hallmark of a true contractor.

Don't forget to invoice.

[LisaContractorUmbrella]

Oops I only meant that BoredBloke is a better VBA programmer than I am. Mainly because I'm not a VBA programmer but can blag a few things here and there

Well you're blagging was very useful

[TheFaQQer]

That hurts

Oops I only meant that BoredBloke is a better VBA programmer than I am. Mainly because I'm not a VBA programmer but can blag a few things here and there

[LisaContractorUmbrella]

It was a bug in Lisa's conditional formatting

I tried to send her your way, though, but to no avail

That hurts

[LisaContractorUmbrella]

It was a bug in Lisa's conditional formatting

I tried to send her your way, though, but to no avail

It was yes. I had a bug

All sorted now though

[TheFaQQer]

FWIW it seemed to work ok when I tried it

It was a bug in Lisa's conditional formatting

I tried to send her your way, though, but to no avail

[BoredBloke]

FWIW it seemed to work ok when I tried it

=ValidPostCode(B3)

If you wanted to use it in your VBA rated that having a User defined Function on the worksheet you'd have to loop through the postcodes and throw each one to your function.


for i = 2 to lLR
cells(i,15) = ValidPostCode(cells(i,14))
Next i

[LisaContractorUmbrella]

I've emailed you my spreadsheet

You're the best

[TheFaQQer]

No worries TF - thanks for posting

Nope still doesn't work. The macro just returns a blank in the field that I would expect to find the formula

I've emailed you my spreadsheet

[LisaContractorUmbrella]

No worries TF - thanks for posting

Nope still doesn't work. The macro just returns a blank in the field that I would expect to find the formula

[TheFaQQer]

Sorry, missed your email.

Assuming you want a macro to insert a new column in column P (data being in column O), then validate the post codes using the function, try

Sub PostCode()
    Range("P1").Select
    Selection.EntireColumn.Insert
    ActiveCell.FormulaR1C1 = "=IF(ValidPostCode(RC[-1]),""OK"",""Incorrect"")"
    Range("P1").Select
    Selection.Copy
    Range("O1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Activate
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

That would stop if there are any blanks in the column, though.

[LisaContractorUmbrella]

Morning -sorry to be a bother but I have a teeny problem with this now

The calculation incorporating the expression works perfectly except when I come to incorporate it in a macro - what have I missed???

Sub Postcode()
'
' Postcode Macro
' Macro4
'

'
Columns("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(ValidPostCode(RC[-1]),""OK"",""Incorrect"")"
Range("P3").Select
End Sub

[LisaContractorUmbrella]

That's good. And do you understand it now?

Absolutely

[mudskipper]

It works Thanks for all your help guys - really appreciated

That's good. And do you understand it now?