Automated =Left, =Left(Right), =Right & Concatenate

Hi All,

I'm back once again with a problem I think should be relatively easy to solve but I'm guessing I'am showing my novice skills are building code. But I am learning

Anyway down to the problem at hand

I have a list of Rows with Data about the way a product is performing for a customer in one row which has locality data I need to separate using the =left , =left(right) , and =right function but the issue is that I need to do this in VBA and the locality column will move its row location over time so I need to use a Find "Locality" column by name within the Left and right function and then need to replicate it till the last row of data.

I have attached an excel file with the left and right cells done in standard excel formula but I need to replicated in VBA, if anyone could help that would be greatly appreciated.

Regards

I don't understand your request. Why would you use the Find "Locality"? Will its column change with time? Can you explain a little more, please?

Hi p24leclerc,

Yes due to the database this data is pulled out of the location of the "Locality" Column will change as new columns of data are added so I need the report to be able to find the column based on the name of the column header.

So basically what i need to do is with the locality row how it has 3 postcodes combined into 1 line I need the break them apart. I am planning to do this with the Left and right functions.


here is the basic code
ActiveCell.FormulaR1C1 = "=Left(RC[-9],4)"

I need to change the RC[-9] to as something like below

ActiveCell.FormulaR1C1 = "=LEFT((cells(2,locality),4)"

thanks for your time

i hope this makes more sense

What about the location of the 3 columns containing the LEFT, RIGHT,etc value? Will they change place also? Would you like the macro to create new columns for them?