If statement

[D_N_L]

Hi all,

I'm struggling with the last piece of my macro! Please help!

So far my macro does the following after it is run:

1. Asks the user to select an excel file and then opens it
2. Asks the user to select a range (of postcodes/zipcodes) within that file
3. Then using the range the macro inserts a space 3 characters from the right in each cell
4. Then it replaces all the 'O's with '0' (zeros)
5. Then it inserts a column to the left of the range the user has selected.

What I need the macro to finally do is an if statement against a fixed range of correct postcodes/zipcodes which is located within the same workbook (that the macro is fired from). The sheet with the correct codes is called PDCS and the range with the correct codes is A1:A33679.

What I would like the if statement to do is to check each cell of the user defined range and put a 'Correct' or 'Incorrect' in the column that is to the left of the range the user has picked (as is created in step 5 above). The 'Correct' will appear for those where the code matches those in the PDCS list and 'Incorrect' for those that don't.

I know how to do this with a static range but not how to create it so it refers to the range that the user picks. I have pasted my code so far if it helps.

Thanks for any help you can provide.

Please Login or Register  to view this content.

[Steffen Thomsen]

Hi,

You could propably do it like this

In macro3

Please Login or Register  to view this content.

Steffen Thomsen

[D_N_L]

Hi Steffen,

It's that bit I'm struggling with, i.e. actually compiling the if statement. Also, I wouldn't want it to refer to Column D, as hypothetically it could be any column as the range will be defined by the user of the macro.

[Steffen Thomsen]

Hi,

It seems to me as there is no fixed place the postal codes exist and theres no header in the user selected range that you need to match the other way round.

I dont have much time right now, i will look in to the code bit later.

I would open the PDCS and create an Array of the range

After that i would check if the Arrayvalues exists in the userselected range wit the Application.WorksheetFunction.Find method.

You could look in to a solution like this.

Steffen Thomsen

[D_N_L]

Hi,

The correct postcodes that I need to compare against will always be located on the PDCS sheet, A1:A33679. The user defined range, as you correctly say, could be anywhere.

I think your last solution is what i need to do but i don't know how to compile the code. Bit of a newbie!!

[Steffen Thomsen]

Hi,
Could you upload an example workbook, it's easier for me to see what you need igf you do that.

Steffen Thomsen

[D_N_L]

Hi Steffen,

Thank you for your continued help. I have attached an example workbook. To explain:

Sheet 1 contains an example of the range the user will pick (highlighted in red)
Sheet 2 shows what it'll look like after the user has clicked the macro command button.
Sheet 3 contains the PDCS postcodes. In the real workbook this range will be A1:A33679

Please remember that the range the user picks in Sheet 1 could be any vertical range.

Hope that makes sense. Thank you.

[Leith Ross]

Hello Plumby,

I know Steffen is helping you with this. I did some work earlier this year in validating zip codes and UK postcodes. Rather than save all the postcodes on a hidden sheet, you can use VBA to the do the validation using the rules for inward and outward codes.

I have added the macros below to the attached workbook along with a button on "Sheet1" to run the macro. I hope both of you find this useful.

UK Postcode Validation Macros

Please Login or Register  to view this content.

[D_N_L]

Hi Leith,

Thanks very much for the macro. Really appreciate any help I get from yourself or Steffen. The thing is - could you alter your macro so it does the if statement against the hidden sheet (or sheet 3 in this case) as per my original request? The way you have done the macro so it only works on the selected range (which could be any vertical range as defined by the user) is brilliant. However I just need the correct/incorrect part to refer to the sheet with the correct postcodes on it (in this case sheet 3).

I defo hear what you're saying about doing it your way its just that prior to this I have macros performing other tasks, such as changing all the 'o's to '0's, etc. Essentially the postcodes will be very dirty!

Thank you Leith.