convert formula to VBA Function

Hi all, I am new to VBA functions. I have a requirement to extract substring from a delimited file, and could do correctly using excel formula, though it ended up in long complicated ones! Now I am looking to consolidate all into one button. I tried VBA but some functions when converted to VBA are not working.(Eg . SUBSTITUTE). Is there a way I can use the exact Excel Formula in a VB / or any other way? Should I use Command Button ? Regards,

Hi, Thanks a lot for the reply. I have attached my requirements in a word document with excel sheet embedded.
Regards,

It is unclear what your algorithm is for picking out the red parts. It's not consistent. This is what's in your Word file:

4. In PATH(column E), I need only specific info. This is too lengthy and messy. This delimited file is always have the same structure. I need only the red fond parts to populate as rest of the columns, say from column C , D, E, F and G.

SOC: Respiratory, thoracic and mediastinal disorders: 10038738; HLGT: Respiratory disorders NEC: 10038716; HLT: Upper respiratory tract signs and symptoms: 10046313; PT: Throat irritation: 10043521; LLT: Throat irritation: 10043521

For all fields except one, you want to take the text following the label (SOC, HLT, etc.) up until the ":" that precedes the diagnosis code. But after HLGT you also highlighted the next label NEC, which has a colon after it but no description, just a code. It would help if you break this out to show how it is supposed to be broken by column.

I don't know if you want this

Respiratory disorders NEC: 10038716

or this

Respiratory disorders

NEC: 10038716

Or this

Respiratory disorders

10038716

Try

Please Login or Register  to view this content.

Hi, thanks a lot again! It works the way I want. however I made some silly error! The digits are not required. Only the 'text' part is required. ( like " Respiratory disorders NEC "). To make it more clear I have attached an excel sheet, with expected result, comments in Blue in Result tab. Some additional requirements if possible:

1. All parts copied over from 'report' tab can be made locked ?
2. Additional column at the end, Comments. (This should be 'free text' editable field, intended to make entries by users)
3. Some cosmetic enhancements to look like a more professional report.(Report header, Report time and date. Need 3 rows at the top.) Filter should be ON at row 4, throughout the column headers, Grid line view is not required.)
4. Column title also added.
5. Additiaonal column A added.(Details- given in the attached sheet)

I still prefer VBA, as I need not worry much on maintaining 5 different formula. User may inadvertently delete the blank cell, which in fact has some formula ? Should locking securely will help ?

Regards, Pras

Originally Posted by prasjohn

Hi, thanks a lot again! It works the way I want. however I made some silly error! The digits are not required. Only the 'text' part is required. ( like " Respiratory disorders NEC "). To make it more clear I have attached an excel sheet, with expected result, comments in Blue in Result tab. Some additional requirements if possible:

I don't understand this, coz you don't have such data in your latest file.

Please Login or Register  to view this content.

Hi Thanks again..I did mention all data in my earlier file. However, use the latest file only, for now, as I have made some minor changes to raw data headers. Please find the attached spreadsheet with my comments.See all Yellow highlighted fields.

Column A ----- Header is correct, (DETAILS) , However data is wrong(from 'scratchy Throat' onwards). This data should be under 'Verbatim' in Column B

Column A ----- Data should be a concatenated field from REPORT tab. (Excel formula =CONCATENATE(Report!A2,"/",Report!F2,"/",Report!N2,"/",Report!O2) . Fields used here are column A,F,N and O, separated by "/".

Column B Header is correct, (VERBATIM), however data is wrong,. This data is NOT required. Actual data should come from 'Name' column of Report Tab.(Scratchy throat ..etc.). You have this data under column A now.

Column C to H ----- Correct, No changes required.

General ----- GRID lines should be OFF

General ----- Click button for initialing the program.(in Report tab) as given earlier


Thanks!, Hopefully this will conclude.
Regards,PJ