CONVERT worksheet formula into vba code

[tony0710]

Hi

I'm manually entering a 10 digit code into Col A; at this moment in time I have the following formula in Col B:

Please Login or Register  to view this content.

However I have to drag and fill all the cells in Col B with the formula - is there anyway of just entering the code into Col A and excel automatically inderts the result based on the above formula into Col B? VBA coding??

[royUK]

Why is it such a problem to drag the code? If you hover the cursor over the bottom right corner of the formula cell until it changes to a +, double click & the formula will autofill to the end of the sheets used range.

[tony0710]

RoyUK: I understand what you are saying and I am happy to do that - BUT this worksheet is going to be used by different people, who (pardon my bluntness) are THICK!! They don't have that basic knowledge; or perhaps are just being to damn lazy to do it! All they will do is complain and moan that the spreadsheet is not doing what it's meant to do. So I thought that if I wrote the whole spreadsheet in vba - in such a way that the user only has to enter the first code and the rest will be automatically populated.

I'm just sorry that I have to deal with such "dense" staff.

Workbook attached that I need to convert all to VBA - just need the right pointers from you the GURUs to make it happen.

[royUK]

You could use code like this

Please Login or Register  to view this content.

[tony0710]

I put that code in - and nothing happens

[royUK]

Put it where/ It adds the formula as the user completes the entries in the three cells to the left

[tony0710]

sorry I still don't understand

[royUK]

See the example. Fill data into Columns A to C , D will autofill with the formula

[tony0710]

Neat idea - but I think that you are missing the point.

I want the user to manually input the 10 digit code into Col A and excel to auto-populate Col B to Col D, based on the formulas present in the cells above.

[ConneXionLost]

I'm not able to download your spreadsheet to look at it (firewall at my workplace), but it sounds like all you need is to add an IF statement to your formula, for example:

=IF(ISBLANK(A13),"",DATE(2000+MID(A13,6,1),LOOKUP(MID(A13,5,1),{"1","2","3","4","5","6","7","8","9","X","Y","Z"},{1,2,3,4,5,6,7,8,9,10,11,12}),MID(A13,3,2)))

Hope this helps.

[royUK]

Then amend the code to this

Please Login or Register  to view this content.

[tony0710]

I got a code from:

Adopted the Code for my data sheet:

Please Login or Register  to view this content.

Works a treat!!!

But thanks for trying and all your patience - I'm still going to try your suggestion too.

Marking this thread as resolved.

[royUK]

That's basically what my second code does in two lines

[tony0710]

You are right!!

So as you have less lines of code - it means that you are the ULTIMATE GURU

[tony0710]

ok - slightly off target from my original question - but this is to do with RoyUk's answer:

Can the code be applied to all worksheets that have formulas?

[royUK]

It depends on the page set up. If you have the same criteria - change column A, formulas in B to D yo can use the WorkBook SheetChange event.

If the layout is different the code would need to be changed to be more dynamic.

[gulzar_7862002]

I have three coulmn excel that I filled every day - The columns are
Pull Date (The date on Which I pull Data) , Resevation Date (Dates on Which Guest booked) Month , MARKET, SEGMENT, ROOMS, REVENUE.
I have created a tool with SUMIFS formula which gives me exect output, but It takes lot of time to calculate the cell. Can I covert the same into VBA. my formula is in excel = =IF(E$3 (Reservation Date)<$C$3( Data Pull Date) ,SUMIFS(Actual!$F:$F(Room Nights total),Actual!$E:$E (Segment Match),'May 2013'!$B5(Segment Match with output),Actual!$B:$B(Reservation Date match),'May 2013'!E$3)(Reservation Date match with output),SUMIFS(BOB!$F:$F(Room Nights total),BOB!$E:$E (Segment Match),'May 2013'!$B5 (Segment Match with output),BOB!$B:$B (Reservation Date match),'May 2013'!E$3 (Reservation Date match with output),BOB!$A:$A (Pull Date match),'May 2013'!$C$3(Pull Date match with output))).

If you want sample worksheet just let me know.

Thanks in advance.

[arlu1201]

Gulzar,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[gulzar_7862002]

Sorry admin, I will take care of the same next time