Worksheet Function in VBA

[mar050703]

Hi

I currently have the following formula in Column C

 =IF(A31=A30,CONCATENATE(TEXT(A31,"ddmmyy"),SUM(RIGHT(C30,3)+1)),CONCATENATE(TEXT(A31,"ddmmyy"),101))

Basically this looks at the value of Column A, and if the date is the same as the row above, It will add 1 to the last digit, (which is the date and 101), so would become 102, but if the date is not the same would become the date and 101.

What I am now needing to do is do this in VBA. I am arriving at this cell via Activecell and Offset, as at fist the code goes to the first available cell in column A and enters data from the userform across that row.

The first thing the userform will ask for the date (which may not be todays date).

I am at a complete loss what code this might look like.

Can someone help please

 

[SkipVought]

hi,

What I am now needing to do is do this in VBA.

And the purpose for NEEDING to do this in VBA is?

I raise this question because if it is merely to propagate the formula as new rows are added to a table, consider using a Structured Table which will obviate the need for VBA for this purpose.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Hello

Thanks for response, basically I want to do it in VBA so that the last row is data, and so the user does not have to copy the formula down. So yes it is to propagate as new rows of date are added.

How can I do it in a "Structured Table"

Thanks

 

[SkipVought]

1. select in your table.
2. Insert > Tables > Table
3. You can change the look of the ST easily, using the context sensitive Table Tools > Design tab.

whatever formula(s) you have in this table will be propagated to the next row whenever you TAB to the next row, which ADDS a new row to the ST.

STs are a fabulous tool in Excel. Read up on this in Excel HELP!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Thanks again Skip

I will look into that.

Will I need to do anything different in my VBA coding, or can I still find the first empty row and get vb to enter the data from the userform?

 

[SkipVought]

You should not need to.

However, it may be necessary to resize the ListObject range. So if your current code does not cause the table to expand by one row, then post back for help.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Skip

Thanks. No my current code loops until activecell is empty in row A, and then from there the data is entered.

Thanks

 

[SkipVought]

Thanks. No my current code loops until activecell is empty in row A, and then from there the data is entered.

...and then, can you see that the Structured Table has expanded?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

I will do a test when I get a moment.

Thanks

 

[mar050703]

Thank you so much that is really helpful.

 

[SkipVought]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Skip,

Forgot to ask, one final thing regarding this cell which in real life will be column C, how do I then get a message box telling me what the answer is:

Something along the lines of MsgBox "The Order Number is : C?", vbokonly (in this case 011213101). Ideally if I can have whatever is in C to be on a 2nd line of the message box and bold.

Thanks again.

 

[SkipVought]

Why in a MsgBox?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

I would like it in a message box as this number is the order number, and when recording it on the paper file will reduce scope for error (ie writing a number from the line above).

Thanks

 

[combo]

Why not dynamic comments and UDF? Here you can find a way to link comment with cell contents. You can create helper column with ChangeComment function, with one argument pointing to cell with comment, second to cell with required text. You can easily extend comment text template in the function

combo